1

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?

  • You need to preface your time with a datevalue method, so above would be =C79-3&datevalue("9:00"). I do question your logic though you would need to pass a sum on your numbers as you cannot use & on integers.. – Datanovice Oct 8 '18 at 7:33
0

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.

0

So not sure if this is what your looking for

=TEXT((DATEVALUE(G5) +5), "MM/dd  9:00")
  1. TEXT is a built-in function.

  2. Datevalue is used to reference a date and then add a number of days to it.

  3. Text(value, Formatting) You can use google sheets format options to come up with a custom value. In the example above i reference a cell that returns the format you asked and then added the time right after it. You can play with it but essentially that's what you requested. Included additional format options for Sheets below.

Google Date format

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.