1. ## 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. 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. 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

4. 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. 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. 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. 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. 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

9. Very observant as always Zeddy. That would mean I would have to change the cell format to:

d mmmm yyyy, hh:mm

10. 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
•