Time and Date Summing

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Time and Date Summing

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

    Default Time and Date Summing

     
    I have one columb with Date, I have a second columb with Time. mm/dd/yy and 16:22 (military time)

    In the second row I have a newer Date and Time.

    I would like to figure the amount of hours ( to the tenth) between the two rows.

    Could you please tell me what the formula is?

  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time and Date Summing

    make sure the two date columns are formatted the same, in your formula column, take latestdate - earlierdate. format the cell to number w/ 1 decimal point.

    if you want hours add * 24 in your formula cell.

    hope this helps.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    My Blog @ ExcelTables.com
    (If you would like comments in any code, please say so.)

  3. #3
    Board Regular
    Join Date
    Aug 2003
    Location
    Boston
    Posts
    174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time and Date Summing

      
    Simply add the date and time for each row together to combine into one date reading that XL will read.

    Then you can subtract one date from the other, the resulting number is a difference in "days" which is waht Xl uses for times.

    Once you have this, multiply it by 24 to get it into hours and format the cell to be #.0 to give you "tenths"

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    DateTimeCombined
    2
    1/20/200416:00:001/20/2004 16:00
    3
    1/22/200411:30:001/22/2004 11:30
    4
    Difference43.5
    5
    Single Formula:43.5
    6
    SGH Template

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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
  •  

 

 
DMCA.com