Google Sheets – Fix Formula for Dates with Static Time

google sheetsgoogle-sheets-timestamp

I'm trying to make a formula that takes a date formatted like this

"10/7" and will spit out the date 3 days before it.

So I make a formula 

=C79-3

and that spits out the date. However, I also want there to be a pre-determined time following the sheet which is "09:00"

However, when I input a date of 10/10 in C79 and use this formula 

=C79-3&"09:00"

I get an output of "433800.375"

What part of the formula is incorrect here?

Best Answer

Using the formula you provided I wasn't able to see the same result

when I used 

=C79-3&"09:00"

I saw 4338009:00 becaseut he "& attached the string "09:00" the the math result.

to get the result you want you need to use:

=C79-3+9/24

that will subtract 3 days from the date in C79 and then add 9 hours (9/24). If you meant 9 PM then you would add (21/24)

The formula with (9/24) does give me the result you mentioned. To get it into a recognizable form change the format to date time. The result will be

10/7/2018 9:00:00

Also the date 10/10 is assumed to be this year. It is always best to enter the date with the year to make it clearer.

Related Topic