Date formula do not work in Machines with american date format
I had this question after viewing how to generate quarters with formulas in Excel.
I had this solution by Alan and it only works if the windows regional setting date format is DD/MM/YY european
i open in a machine with american date format MM/DD/YY then formula return blank and if i remove IFERROR then it is VALUE error.
how can this be fixed that can work with both or if at least if it can work with american date format.
thanks.
EE-29068235-Version1.xlsx
I had this solution by Alan and it only works if the windows regional setting date format is DD/MM/YY european
i open in a machine with american date format MM/DD/YY then formula return blank and if i remove IFERROR then it is VALUE error.
how can this be fixed that can work with both or if at least if it can work with american date format.
thanks.
EE-29068235-Version1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As per answer to the previous question, it is a simple solution of switching the format in my suggestion:
Previous
=TEXT(EOMONTH($A2,3),"dd/mm/yyyy")&" - "&TEXT(EOMONTH($A2,5),"dd/mm/yyyy")
US Format
=TEXT(EOMONTH($A2,3),"mm/dd/yyyy")&" - "&TEXT(EOMONTH($A2,5),"mm/dd/yyyy")
As before, change the bolded numbers as you copy across, or refer to cells containing the numbers
3 & 5
6 & 8
9 & 11
See attached, I have copied the file uploaded by Ejgil but replaced the formulas with mine. I have also inserted a row above the data into which I have added the numbers for the month offset and in B1 there is an option to add US or UK and the date format will adjust.
Flora-Quarters-EE-29068235-Version1.xlsx
Previous
=TEXT(EOMONTH($A2,3),"dd/mm/yyyy")&" - "&TEXT(EOMONTH($A2,5),"dd/mm/yyyy")
US Format
=TEXT(EOMONTH($A2,3),"mm/dd/yyyy")&" - "&TEXT(EOMONTH($A2,5),"mm/dd/yyyy")
As before, change the bolded numbers as you copy across, or refer to cells containing the numbers
3 & 5
6 & 8
9 & 11
See attached, I have copied the file uploaded by Ejgil but replaced the formulas with mine. I have also inserted a row above the data into which I have added the numbers for the month offset and in B1 there is an option to add US or UK and the date format will adjust.
Flora-Quarters-EE-29068235-Version1.xlsx
ASKER