Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Adding hours to a date and time

    Dear loungers,

    I have a column that contains a data and time. This will be used in a calculation where what i am adding is either days or hours. How do I do this - since the column is both date and time the functions DATE and TIME don't work.

    For Example:
    1 April 2015, 12:00 plus 8 hours should give me 1 April 2015, 20:00
    1 April 2015, 12:00 plus 20 hours should give me 2 April 2015, 10:00

    Any ideas?

    thank you............... liz

  2. #2
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    To Myself,

    if you have a column with the hours and minutes and do a normal addition it will work
    if you need to add to a date in a formula with a value in a formatted column I don't think it can be done

    liz

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi lizat

    ..see attached file.
    Does this give you what you want???

    col [B] has a custom format of 0.00 "hrs", so if you enter 8 you will see 8.00 hrs

    zeddy
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi lizat

    ..and when you add the hours value, you just divide it by 24 to get the value in days, and then add that to your first date/time value.

    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,202
    Thanks
    49
    Thanked 987 Times in 917 Posts
    Dates are just numbers, where 1 = 1 day and an hour = 1/24. As long as you keep your inputs in the correct format all will be well.
    e.g. to enter 8 hours type: 8:0, 8:00 or 8:
    If you want to use real numbers as hours you need to convert by dividing by 24.

    cheers, Paul

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Lizat,

    Set you cell formatting as mmmm dd, yyyy hh:mm for both the start date (Col A) and the cells with the time increments you are adding (Col B). Use this formula in column B:
    Cell B1 =A1 + TIME(9,0,0)
    Cell B2 =A2 + TIME(20,0,0)

    lizat.png

    HTH,
    Maud

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    I see that you have the day first. In that case use this format: dd, mmmm yyyy hh:mm

    lizat2.png

    1 April 2015, 12:00 plus 20 hours should give me 2 April 2015, 10:00
    Should be: 1 April 2015, 12:00 plus 20 hours should give me 2 April 2015, 08:00

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud

    The comma is in a different place in post#1, and there is no leading zero for the day.

    Attachment 42082

    ..so I updated my file too

    zeddy
    Attached Images Attached Images
    Attached Files Attached Files

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Very observant as always Zeddy. That would mean I would have to change the cell format to:

    d mmmm yyyy, hh:mm

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud

    I wouldn't say my eyes were brilliant, but on a clear night I can see as far as the moon.


    zeddy

Posting Permissions

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