Thread: Change date from m/d/y to d/m/y

1. Hi,

I need help to convert a date from m/d/y (03/04/2009) to d/m/y (04/03/2009)

The W/B is attached. What confuses me is that there is time included? I need to retain the time in the output too.

TIA

2. [quote name='Rudi' post='765586' date='16-Mar-2009 13:43'][/quote]
The cells are currently formatted as dd/mm/yyyy hh:mm:ss. You could format them as mm/dd/yyyy hh:mm:ss.

3. I can't open your wb as I am still on 2003 but is it not just a simple case of custom format dd/mm/yy hh:nn ?

4. TX... yes this could be done just to display it correctly, but I need it to be 4 Mar 2009 so it calculates correctly too.

I have used the DATE() function to reconstruct it, but I cannot get the date and time back into a single unit again. If only there was a DATETIME() function.

Sorry Jezza...here is the latest .xls format.

5. You could use a formula like this:

=DATE(YEAR(A2),DAY(A2),MONTH(A2))+MOD(A2,1)

in row 2 and fill down.

6. Tx Hans...it works perfectly. Could you explain the MOD(A2,1) part? AFAIK, you are taking the value in A2 and dividing it with a remainder of 1?? How does this correct the output?

TX

7. [quote name='HansV' post='765595' date='16-Mar-2009 15:18']You could use a formula like this:

=DATE(YEAR(A2),DAY(A2),MONTH(A2))+MOD(A2,1)

in row 2 and fill down.[/quote]

OK...with some further experimenting....that is so clever!!!

For example: 39876.23547 is the date and time in raw format.
MOD(39876.23547,1) returns 0.23547

This is the time that you simple add to the date! REAL CLEVER! Cheers for that tip!

8. [quote name='Rudi' post='765593' date='16-Mar-2009 13:14']TX... yes this could be done just to display it correctly, but I need it to be 4 Mar 2009 so it calculates correctly too.

I have used the DATE() function to reconstruct it, but I cannot get the date and time back into a single unit again. If only there was a DATETIME() function.

Sorry Jezza...here is the latest .xls format.[/quote]

You can just add the time:
=DATE(YEAR(A2),DAY(A2),MONTH(A2))+TIME(HOUR(A2),MI NUTE(A2),SECOND(A2))

9. [quote name='rory' post='765600' date='16-Mar-2009 15:46']You can just add the time:
=DATE(YEAR(A2),DAY(A2),MONTH(A2))+TIME(HOUR(A2),MI NUTE(A2),SECOND(A2))[/quote]

Hi Rory,

That is true. It dawned on me with Hans's reply that one should ADD the time. I was initially using concatenate to join it up. This did NOT work!!! Silly me!

10. [quote name='Rudi' post='766268' date='19-Mar-2009 19:16']Hi Rory,

That is true. It dawned on me with Hans's reply that one should ADD the time. I was initially using concatenate to join it up. This did NOT work!!! Silly me![/quote]

I use ASAP utilities (www.asap-utilities.com); under menu 8, option 8 there is a very flexible way to do this. The utilities are free for personal use; I use them every once in a while for 'special' things...

11. TX ErikJan. I have used this utility in the past, but since I upgraded to Office 2007, I have not reinstalled the addin. But tx for the reminder.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•