Solved: conditional format date

[wibbers2000]

I have a attachd a very small spreed sheet to explain.

I have 3 columns

A B C
38786 38786 38786
16/02/2007 T 39129

I would like to conditional format column C

if Column B = a number then format C as MM/DD/YYYY

If Column B = T the format C as DD/MM/YYYY

I know it wont change it at the core, but it will make the report look consistant.

Regards

[Bob Phillips]

Why not just apply a date format to the whole of column C?

[wibbers2000]

The reason is simply that the numbers in column A convert to american format and the text date are Australian.

The request above is the only way I work out that to say if column A = a number and therefore an american format of mm/dd/yyyy I can change the displayed format to dd/mm/yyyy. Where as the "date" in column A is the correct date and there should stay in the same format as it is currently displayed


Hope this clarifys it...
:think:

[zv735]

Try to uses this formular in Column C

=IF(ISNUMBER(B1),TEXT(A1,"MM/DD/YYYY"),TEXT(A1,"DD/MM/YYYY"))


it work or not

Thank you :)

[Bob Phillips]

Excel should handle regional settings itself, so if it is a number, display it in the date format that you have setup.

[wibbers2000]

ZV734. this code did the trick thanks.

XLD. I looked into the problem further and have identified what is happening. I am exporting the data from Myob. the regional settings for the computer are for Australia, however the regional settings in Myob are for US (still not sure how and why). However, because the US settings are MM/DD/YYYY, anything greater than the 12th are recorded as Text as there are obviously only 12 months. The code that zv735 does the trick untill I resolve the issue with Myob.

Either way, thanks to the both of you for your help.

Regards
Paul