time calculation spanning midnight

[marshaji]

I have a spreadsheet in which i have a date column, start time finishtime and duration.

some of the finish times are after midnight and i require to calculate how many hours are after midnight as they have a different pay rate.

Midweek eveing hours are paid say £56.00 and overnight £65.00 the overnight rate is also applicaable all weekend hours.

What i require is to calculate how many hours at each rate to ensur ethe correct number of hours at the correct rate is actually paid

 

[NBVC]

If your start/end times are in B2:C2, respectively, then to calculate hours past midnight, try:

=MAX(0,(C2<=TIME(7,0,0))*(C2-TIME(24,0,0))*24)

this formula assumes that "after midnight hours" happens until no later than 7:00 AM

you may have to play around with the TIME(7,0,0) part depending on your start times and latest expected overnight hours time.

the Weekday formula can help determine if the weekday is a weekend...

=WEEKDAY(A2,2)>5 checks if date in A2 is Saturday or Sunday.