Force specific Time to a date of a cell

Question

What I am trying to get is to add a specific hour given by me to a date that exist in other cell. So I want to get 2015-12-19 08:30

My formula is =DATE(YEAR(I2);MONTH(I2);DAY(I2)+3) and gets me the 2015-12-19 , how can I add now the time 08:30?

I have already formatted the cell to aaaa-mm-dd hh:mm

I tried several formulas but didnt work.

Thanks.

Answer 1

Try concatenating the date cell with the cell that returns the current time (or whatever time you would like to display).

If A1 contains your date values and B1 contains the time you would like to return:

=concatenate(A1,B1)

You can use =TEXT(NOW(), "hh:mm:ss AM/PM") in the time field to display the current time.

Answer 2

Use:

=DATE(YEAR(I2),MONTH(I2),DAY(I2)+3)+TIME(8,30,0)

with the proper formatting and your regional separator (;)