Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    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 ?
    Jerry

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    Attached Files Attached Files
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use a formula like this:

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

    in row 2 and fill down.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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!
    Regards,
    Rudi

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    [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))
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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!
    Regards,
    Rudi

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    Regards,
    Rudi

Posting Permissions

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