Thread: [Solved] Timesheet formula
View Single Post
 
Old 05-27-2011, 07:08 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

It doesn't matter how you format the cell provided that the underlying piece of data is a date and time.

These formulas assume that if a shift starts on a Saturday or Sunday then a standard shift will be 12 hours. If the shift starts on any other day then a standard shift will be 8 hours. They also assume that Start Time < End Time.


In C2, with the cell formatted as [hh]:mm
Code:
=MIN(B2-A2,IF(OR(WEEKDAY(A2)={1,7}),1/2,1/3))
In D2, with the cell formatted as [hh]:mm
Code:
=B2-A2-C2
Attached Files
File Type: xls Example.xls (15.0 KB, 17 views)
__________________
Colin

RAD Excel Blog
Reply With Quote