Date Reversal

**Mizery** · 08-20-2008, 09:32 AM

Hello,

I am using a formula to find the oldest date of a list of dates depending on what country an item is from. In my place of work we use profiles so anyone can log on to any computer and access all of their own details etc.

When I use the formula the date is returned correctly in the UK format (DD/MM/YY), and another of my colleagues also. However on some other profiles, the numbers (not the actual dates) are reversed to the American style (MM/DD/YY). The file that the formula is part of is remotely stored, so the formatting does not change.

I am sure there is some local excel setting that is reversing the dates, does anyone have any idea where I might find it? Or any idea how to stop the change.

Unfortunately it is not a simple case of 3-august-08 appearing as August-3-08, instead it appears as 8-march-08. (month titles only used for ease, I the formula uses numbers).

Thanks

**arthurbr** · 08-20-2008, 09:36 AM

Did you have a look at the regional settings in the PC's Control Panel?

**Mizery** · 08-20-2008, 09:58 AM

Yes, unfortunately it's a standard setting that no one can change, so no joy there. Thanks for trying though

**arthurbr** · 08-20-2008, 10:39 AM

If your original date is say "31/12/08" the date looks correct but in fact it is text?

**Mizery** · 08-20-2008, 11:33 AM

No, the original date (taken from an external source) is in the format: 08/19/2008 08:30 AM. When added to the sheet it stays the same, but when found using the formula, the month and date are reversed, so the example above would be 19/08/2008 08:30 AM.

This wouldn't be a problem, but it's not a case of just changing the format, the date is being recognised as 19(month)/08(day)/2008(year). Obviously there is no 19th month of the year, so the result is a fail.

**oldchippy** · 08-20-2008, 11:50 AM

Hi,

Does this work for you, it will change the format to a TEXT format

=TEXT(DATE(YEAR(A1),MONTH(A1),DAY(A1)),"dd/mm/yyyy")&" "&TEXT(MOD(A1,1),"hh"":""mm"":""ss")

Result = 19/08/2008 08:30:00

**shg** · 08-20-2008, 11:51 AM

the original date (taken from an external source) is in the format: 08/19/2008 08:30 AM. When added to the sheet it stays the same

1. It appears in the cell exactly like that?

2. If you change the format to General, does it appear as a number? If not, it is stored as text. If you cannot change the regional settings on the computer, then you'd need a formula and a helper column to get the correct date.

**Mizery** · 08-20-2008, 12:41 PM

Hi, thanks for all of the help. The problem was, as very quickly identified by arthurbr, the regional setting on the PC's. Although we cannot change them, apparently they are set depending on where you are from. The company making things easier or something. I tried the other suggestions before convincing an IT guy to change things. I really thought it was an Excel setting that was hindering progress.

Sorry for any trouble, but you guys have saved me loads of work, so I am grateful.

Thanks