0

I'm using Google sheets and the MAX() function works only on dates with format mm/dd/yyyy. How to get the MAX() of date with format dd/mm/yyyy?|

https://i.stack.imgur.com/uBu4W.png

Improve this question

1 Answer 1

Reset to default
0

When using Excel (or Sheets) it tries to parse strings into dates, but can be a bit hit or miss (more miss) when you use a format different to what the machine you are entering it on is set to e.g. yyyy/mm/dd vs dd/mm/yyyy or dd/mm/yyyy vs mm/dd/yyyy.

Firstly, to check if a date was correctly parsed, try adding a day to it. in your example, in B2 enter =A2+1 and you should get an incremented date. If you get an error it means it's a string, not a date. To use the dates, you need to parse it yourself. In your specific example try next to your original date column =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) and use that for subsequent calculations.

Improve this answer
1
  • Actually, the problem is getting last login of customers. I'm using the formula=MAX(IF(Sheet2!A2:A8 = I2, Sheet2!E2:E8)) (Sheet2 contains login times of all customers, column A is customer id, column E is login date). So, I think I will convert column E (dd/mm/yyyy) to column F (mm/dd/yyyy) with your formula, then replace column E by F. Thanks
    – Vu Le
    Jul 17 at 9:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .