Adding hours to a date and time

Topic

New Reply

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: [INDENT]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
[/INDENT]

Any ideas?

thank you…………… liz

Reply | Quote

Replies

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

Reply | Quote

Hi lizat

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

col has a custom format of 0.00 “hrs”, so if you enter 8 you will see 8.00 hrs

zeddy

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I see that you have the day first. In that case use this format: dd, mmmm yyyy hh:mm

39750-lizat2

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

Reply | Quote

Hi Maud

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

..so I updated my file too

zeddy

Reply | Quote

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)

39749-lizat

HTH,
Maud

Reply | Quote

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

d mmmm yyyy, hh:mm

Reply | Quote

Hi Maud

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

zeddy

Reply | Quote