Certain times formula

[Excelexcel86]

Hi what formula can be used to see if a time is between 06:00 and 14:30

 

[bebo021999]

Assume A1 is timestamp
use:
=AND(A1>=TIME(6,0,0),A1<=TIME(14,30,0))
Or
=AND(MOD(A1,0)>=TIME(6,0,0),MOD(A1,0)<=TIME(14,30,0))
if A1 is Date-Time

 

[Excelexcel86]

Tried the bottom one but it’s saying the formula is missing an opening or closing parenthesis

Also should it be =if(and ath the start

 

[bebo021999]

Opp, sorry,
try MOD(A1,1) in stead of MOD(A1,0)

 

[Excelexcel86]

Thanks can I make that a if and command so I can put what I want to happen if true

 

[Fluff]

How about

=IF(MEDIAN(MOD(A1,1),TIME(6,0,0),TIME(14,30,0))=MOD(A1,1),"Y","N")

 

[Excelexcel86]

How about

=IF(MEDIAN(MOD(A1,1),TIME(6,0,0),TIME(14,30,0))=MOD(A1,1),"Y","N")

Hi that works great but now the problem is if the time is over that and goes into the next day it reads it as true and gives me a high value in time how can the formula be adjusted to have it in the same day also ?

 

[Fluff]

I have no idea what you mean, either the time is between 6:00 & 14:30 or it isn't the day has nothing to do with it.

 

[Excelexcel86]

Item came in warehouse	item left warehouse	hours during work hours
03/03/2022 06.00	04/022022 10.00	should be 8.30 hours

Hi if u look at the example above what I mean is using that formula because it’s gone into another day it calculates all the way till 10.00 next morning when it left the warehouse so it’s showing 16hours because in cell c3 I’m using b2-a2 . So with the formula u gave me can you use an if and command with it to see if the two days are the same as well because if not are true the formula will work I just don’t know how to do this

 

[Fluff]

The formula simply shows Yes or No depending on the time, it does not return the number of hours.