CODEWITHSUNDEEP
if-statementtimegoogle-sheetsgoogle-sheets-formulaarray-formulas
Ryan Fabbro
Ryan Fabbro

Reputation: 69

Google Sheets - If worked past midnight

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")

Upvotes: 0

Views: 1206

Answers (4)

player0
player0

Reputation: 1

use:

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

0

or shorter:

=ARRAYFORMULA(C3:C9<B3:B9)

Upvotes: 3

dreojs16
dreojs16

Reputation: 117

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)

Upvotes: 0

C Mikely
C Mikely

Reputation: 23

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

Upvotes: 2

Ryan Fabbro
Ryan Fabbro

Reputation: 69

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))

Upvotes: 0

Related Questions