+ Reply to Thread
Results 1 to 8 of 8

date and time formatting

  1. #1
    Registered User
    Join Date
    01-26-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    9

    date and time formatting

    Hello,

    I'm trying to translate a date and time that comes in a format of MM DD and HOUR in three different columns, and with just four numbers for the time (ie 1000)

    What I'd like to do is get all the information into a single cell that has the format

    mm/dd hh:mm

    I've tried using the RIGHT () and LEFT() commands to get the date information in, but it doesn't seem to allow me to format the cells where I've done the Right and Left commands. Is there someway I can get the information from these original cells, and still be able to format it correctly so when I plot, it will show the day and time?

    thank you!

    Erik

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,295

    Re: date and time formatting

    Can we see your formula? If you identify the cells where the date and time are held, we should be able to work it out.

    Better still, post a sample workbook with examples of the values you are processing.

    Regards

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,295

    Re: date and time formatting

    It would look something like this:

    =DATE(YEAR(TODAY()),A2,B2)+TIME(LEFT(C2,2),RIGHT(C2,2),0)

    formatted as: dd/mm/yyyy hh:mm

    A2 contains the month, B2, contains the day and C2 contains the 4 digit time.

    Regards

  4. #4
    Registered User
    Join Date
    01-26-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: date and time formatting

    excellent! thanks much for the help!!

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: date and time formatting

    FWIW - on a US config. you might also try:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-26-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: date and time formatting

    @TMSHucks

    I've actually found that your solution doesn't work for rows where the hours is only 3 digits (ie before 10am). I added leading zeros with a custom format, but that didn't help.

    thoughts?

    thanks,
    Erik

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: date and time formatting

    Quote Originally Posted by eturn
    I added leading zeros with a custom format, but that didn't help.
    Altering the format of a cell does not affect the underlying value, eg:

    930

    format as 0000 will display as 0930 but the underlying value of 930 persists from a dependant calculation perspective

    To resolve: you should find applying a Text format (as previously outlined) generates the requisite value.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,295

    Re: date and time formatting

    =DATE(YEAR(TODAY()),A2,B2)+TIME(LEFT(TEXT(C2,"0000"),2),RIGHT(C2,2),0)


    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1