time within business hours?

[AceFI]

hi team,
for the life of me i can't seem to work out a true or false for between work hours.. i've googled and seen responses, however cant seem to make it work.

My sheet is converting epoch time to 24h time;

epoch; 1662370201918
converted; =epoch/86400000+DATE(1970,1,1)
returning = 05/09/22 09:30:02

I'm wanting a true if the time is between 8:00 and 18:00

thanks

 

[arthurbr]

Like this ?

=IF(AND(MOD(A20/86400000+DATE(1970,1,1),1)>8/24,MOD(A20/86400000+DATE(1970,1,1),1)<18/24),TRUE,FALSE)

(A20 holds epoch)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your version, another option

Fluff.xlsm
	A	B
1
2	1662370201918	TRUE
Main

Cell Formulas
Range	Formula
B2	B2	=LET(a,MOD(A2/86400000+DATE(1970,1,1),1),MEDIAN(a,TIME(8,0,0),TIME(18,0,0))=a)