Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Date and Time Format?

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am gathering date and time data in XML. Then dropping the date and time information into excel so my co-workers can easily manipulate the data. However when I drop the date and time information into excel the seconds is truncated. For example 05122002 03:12:32 becomes 5/12/2002 3:12 NO Seconds? Is there an after market formating patch that enables Excel to deal with seconds?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think that is the default format for the date and time you are entering. Did you try and format the cells to include seconds?
    The full time should show seconds in your formula bar. If the seconds are correct in the formula bar then you can show them by formatting the cell correctly.
    Tom

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 20:37, JPLoos wrote:
    I am gathering date and time data in XML. Then dropping the date and time information into excel so my co-workers can easily manipulate the data. However when I drop the date and time information into excel the seconds is truncated. For example 05122002 03:12:32 becomes 5/12/2002 3:12 NO Seconds? Is there an after market formating patch that enables Excel to deal with seconds?
    As Tom said in his response, all you have to do is have the right formatting.
    In this case you would need to Custom Format the entry as

    m/dd/yyyy h:mm:ss


  4. #4
    Board Regular WayneTN's Avatar
    Join Date
    May 2002
    Location
    Tennessee, USA
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I agree that the correct formatting should solve your problem. However, I recently encountered a similar situation where I needed to know how many days (an exact whole number) it had been since the workbook just opened had been updated (modified). I just simply truncated the minutes and seconds off of the last modification date & time and then subtracted that date from today's date. This gave me a whole number of days since the last mod. This way I avoided fractions of days.

    [ This Message was edited by: WayneTN on 2002-05-13 09:06 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-13 09:05, WayneTN wrote:
    I agree that the correct formatting should solve your problem. However, I recently encountered a similar situation where I needed to know how many days (an exact whole number) it had been since the workbook just opened had been updated (modified). I just simply truncated the minutes and seconds off of the last modification date & time and then subtracted that date from today's date. This gave me a whole number of days since the last mod. This way I avoided fractions of days.

    [ This Message was edited by: WayneTN on 2002-05-13 09:06 ]
    Hi WayneTN:

    The date format shows composite date and time. The days only part can be extracted using the INT function ...

    =INT(A1-B1) ... will give you just the whole days and drop the fractional time element.

    If you wanted to pick up the number of full days and fractional part, the hour, minutes and seconds also, you can use the following formula:

    =INT(A1-B1)&"Days and "&TEXT(MOD((A1-B1),1),"[h]:mm:ss")


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    Board Regular WayneTN's Avatar
    Join Date
    May 2002
    Location
    Tennessee, USA
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the explanation. I must correct some wording I used above, however. Upon further review, I didn't technically "truncate" the date/time, I rounded down the LastMod date/time to give me the equivalent of a whole day. The exact code that I used, shown below, was adapted from a related, 4/15/02 post by Ivan Moala (thanks Ivan). :->


    Function LastMod()
    '//Finds the File's last modified date/time
    '//...display depends on cell date/time format
    '//Use: =ROUNDDOWN(LastMod(),0)
    '//Returns: mm/dd/yy with minutes & seconds at zero
    '//...so exact no. of days since last mod can be calc.

    Dim Fso, F
    Set Fso = CreateObject("Scripting.FileSystemObject")
    Set F = Fso.GetFile("C:\yourfilename.xls")
    LastMod = F.DateLastModified

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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