Determine on/off with start & end times

[DPuser]

All,

With a start time and end time I’m looking for a better formula to determine if the current time falls within that range; if it does I would like to capture “IN PROGRESS” or “NOT IN SERVICE”.

In A2 I have a start time, B2 I have an end time and F1 I have the following formula “=MOD(NOW(),1)”

In C2, I have the formula “IF(AND(F1>=A2,F1<=B2),”IN PROGRESS”,”NOT IN SERVICE”

This works great except for when the schedule is from 23:14 - 02:33.
How can I reflect and capture past midnight is still the schedule?

Thanks all

 

[Yongle]

welcome to the forum

Date is an integer
- 3 June 2020 = 43,985
Time is a decimal less than 1
0.5 is 12 noon
Combine the two
43,985.5 is 12 noon on 3 June 2020
Now() is an example of combining the 2

One way
- include Date + Time in start time AND end time AND tested time

simplified example
start time 6pm on 2 June 2020 = 43,984.75
end time 6am on 3 June 2020 = 43,985.25
tested time 12noon on 3 June 2020 = 43,985.5 falls outide the range

Your current method (using the same numbers)
start time 0.75
end time 0.25
tested time 0.5

Another way would be to adjust the start time if on previous day

start time (yesterday at 6pm) 0.75 - 1 = -0.25
end time (same day as test date) = 0.25
tested time noon = 0.5
fails the test - outside range -0.25 to 0.25

start time (yesterday at 6pm) 0.75 - 1 = -0.25
end time (same day as test date) = 0.25
tested time 1am today = 0.0416666666666667
passes the test - between -0.25 and 0.25

 

[DPuser]

welcome to the forum

Date is an integer
- 3 June 2020 = 43,985
Time is a decimal less than 1
0.5 is 12 noon
Combine the two
43,985.5 is 12 noon on 3 June 2020
Now() is an example of combining the 2

One way
- include Date + Time in start time AND end time AND tested time

simplified example
start time 6pm on 2 June 2020 = 43,984.75
end time 6am on 3 June 2020 = 43,985.25
tested time 12noon on 3 June 2020 = 43,985.5 falls outide the range

Your current method (using the same numbers)
start time 0.75
end time 0.25
tested time 0.5

Another way would be to adjust the start time if on previous day

start time (yesterday at 6pm) 0.75 - 1 = -0.25
end time (same day as test date) = 0.25
tested time noon = 0.5
fails the test - outside range -0.25 to 0.25

start time (yesterday at 6pm) 0.75 - 1 = -0.25
end time (same day as test date) = 0.25
tested time 1am today = 0.0416666666666667
passes the test - between -0.25 and 0.25

Thank you; I’ll give that a shot.