Google Sheets - If worked past midnight

0
votes

I am trying to return a value (Yes, or No) if the shift was worked through midnight on a time card.

=IF($P$10=MEDIAN(B3,C3,$P$10),"Yes","No")

This seemed the most promising of anything I was able to find or try, but I am having no luck what so ever.

Furthermore, I would like to add a column of Hours worked since midnight. I am also having no luck with that.

Is there any way I can achieve this in Sheets?

An example. If I got this column to say YES worked past 12AM then I would use that YES in an IF function to calculate how many hours since 12AM was the OUT time.

Another Example. If time in is 4PM and time out is 2AM then this column would say Yes 12AM is between those times

If time in was 7AM and time out was 3PM it would say No 12AM is not between those times.

Look at the TIME CARD sheet MY FULLY EDITABLE SHEET

Formulas I have attempted

=MEDIAN("12:00:00 AM",TIMEVALUE(B4), TIMEVALUE(C4))="12:00:00 AM"
Have Also Used a Cell with 12 AM, and without the seconds.

=AND($P$10>B3,$P$10<C3)

=IF(MEDIAN($P$10,B3,C3)=$P$10, "Yes", "No")
4
if-statementtimegoogle-sheetsgoogle-sheets-formulaarray-formulas
You should ask yourself if filling out hours that are officially not on that date is a good idea. What if I fill out hours on date1 from 08:00PM to 08:00AM and on date2 from 04:00PM to 02:00PM? You would be counting double hours... – dreojs16
what would be a more appropriate approach? Our shifts run through midnight, I can't think of another way to do this. Did you take a look at the sheet (TIME CARD) page to see how I have it set up – Ryan Fabbro

4 Answers

3
votes

use:

=ARRAYFORMULA(IF(C3:C9<B3:B9, TRUE))

0

or shorter:

=ARRAYFORMULA(C3:C9<B3:B9)
2
votes

I've edited your formula on your worksheet with a bit of a workaround. If the day goes from PM to AM then it's true, else it's false. 

=if(and(RIGHT(B3,2)="PM",RIGHT(C3,2)="AM"),"yes","no")

I hope this helps

CMike

0
votes

I Have Solved This! The problem I had was no date data, therefor the times being compared were not accurate.

This little workaround did the trick.

=$P$10+($P$10<B3)=MEDIAN($P$10+($P$10<B3),B3,C3+(C3<B3))

0
votes

Try my hour sheet in your Google Sheet: https://docs.google.com/spreadsheets/d/1brscTwgrCSV4RiTkZurGrsyGRfbPV32BkiRzUTnMtE8/edit#gid=921019529

To check weather hours worked surpass the current date simply use:

=IF(F42<E42;"yes";"no")


=IF(endtime<starttime;midnight_has_passed;midnight_has_not_passed)