Make a End of Working Day Formula

[denzo36]

Hi folks,

Time in Excel always confuses the hell out of me.

I have a data time field e.g. 27/03/04 14:48 and I need to compare that to 6pm on the same day to see if a task was handled before COB.

So really I'm trying to take the date from my date time field and always have the time as 6pm. I can't figure out how to do this

Below shows my 'Created' date time column and what I'd like the formula to do when I enter and copy it down.

Thanks guys

 

[etaf]

date is an integer and time is a decimal part

=INT(A2)+TIMEVALUE("18:00")

BUT that just makes the date and time the same date and 18:00

BUT you need to compare to see if the time was before 18:00
so maybe
=IF((A2-INT(A2))<=TIMEVALUE("18:00"),INT(A2)+TIMEVALUE("18:00"),"later than COB")

Book1
	A	B	C	D
1
2	3/27/24 14:48	3/27/24 18:00		3/27/24 18:00
3	3/28/24 14:42	3/28/24 18:00		3/28/24 18:00
4	3/29/24 18:19	3/29/24 18:00		later than COB
5	3/30/24 11:46	3/30/24 18:00		3/30/24 18:00
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=INT(A2)+TIMEVALUE("18:00")
D2:D5	D2	=IF((A2-INT(A2))<=TIMEVALUE("18:00"),INT(A2)+TIMEVALUE("18:00"),"later than COB")

 

[denzo36]

That works perfectly for what I need, thanks so much, really appreciate it!

 

[etaf]

you are welcome