Filetype Excel XLSX | Posted on 18 Aug 2022 | 3 years ago
Authentication
digital resources
256x Filetype XLSX File size 0.02 MB Source: www.exceldemy.com
Sheet 1: All
| Excel Formula to Calculate Hours Worked Minus Lunch | ||||||
| 12 Hours Format | ||||||
| Start Time | Lunch Starts | Lunch Ends | End Time | Total Time of Work (Hrs.) | Formula | |
| 9:00 AM | 1:00 PM | 2:00 PM | 6:00 PM | 8 | =SUM((C6-B6)+(E6-D6))*24 | |
| 9:15 AM | 1:15 PM | 2:00 PM | 6:15 PM | 8.25 | =SUM((C7-B7)+(E7-D7))*24 | |
| Start Time | Lunch Starts | Lunch Ends | End Time | Total Time of Work (Hrs.) | Total Time of Work (Hrs. & Mins.) | Formula |
| 9:00 AM | 1:00 PM | 2:00 PM | 6:00 PM | 8 | 8 Hrs. 0 Mins. | =INT(F10)&" Hrs."&" "&TEXT((F10-INT(F10))*60,"0")& " Mins." |
| 9:15 AM | 1:15 PM | 2:00 PM | 6:15 PM | 8.25 | 8 Hrs. 15 Mins. | =INT(F11)&" Hrs."&" "&TEXT((F11-INT(F11))*60,"0")& " Mins." |
| Start Time | Lunch Time (In Hours) |
End Time | Total Time of Work | |||
| 9:00 AM | 1:00 | 6:00 PM | 8 Hrs. and 0 Mins. | Custom Number Format h" Hrs. and "m" Mins." Applied | ||
| 9:15 AM | 0:45 | 6:15 PM | 8 Hrs. and 15 Mins. | |||
| 24 Hours Format | ||||||
| Start Time | Lunch Starts | Lunch Ends | End Time | Total Time of Work (Hrs.) | Formula | |
| 9:00 | 13:00 | 14:00 | 18:00 | 8 | =SUM((C21-B21)+(E21-D21))*24 | |
| 9:15 | 13:15 | 14:00 | 18:15 | 8.25 | =SUM((C22-B22)+(E22-D22))*24 | |
| Start Time | Lunch Starts | Lunch Ends | End Time | Total Time of Work (Hrs.) | Total Time of Work (Hrs. & Mins.) |
Formula |
| 9:00 | 13:00 | 14:00 | 18:00 | 8 | 8 Hrs. 0 Mins. | =INT(F25)&" Hrs."&" "&TEXT((F25-INT(F25))*60,"0")& " Mins." |
| 9:15 | 13:15 | 14:00 | 18:15 | 8.25 | 8 Hrs. 15 Mins. | =INT(F26)&" Hrs."&" "&TEXT((F26-INT(F26))*60,"0")& " Mins." |
| Start Time | Lunch Time (In Hours) |
End Time | Total Time of Work | |||
| 9:00 | 1:00 | 18:00 | 8 Hrs. and 0 Mins. | Custom Number Format h" Hrs. and "m" Mins." Applied | ||
| 9:15 | 0:45 | 18:15 | 8 Hrs. and 15 Mins. | |||
| Day | Start Time | Lunch Starts | Lunch Ends | End Time | Total Time of Work (Hrs.) | Formula | |||
| Sunday, July 08, 2018 | 9:00 AM | 1:00 PM | 2:00 PM | 6:00 PM | 8 | =SUM((D3-C3)+(F3-E3))*24 | 8.00 | 8:00 | |
| Monday, July 09, 2018 | 9:15 AM | 1:15 PM | 2:00 PM | 6:15 PM | 8.25 | =SUM((D4-C4)+(F4-E4))*24 | 8.25 | 8:15 | |
| Tuesday, July 10, 2018 | 9:00 AM | 1:15 PM | 2:15 PM | 6:30 PM | 8.5 | =SUM((D5-C5)+(F5-E5))*24 | 8.50 | 8:30 | |
| Wednesday, July 11, 2018 | 9:30 AM | 1:45 PM | 2:45 PM | 6:45 PM | 8.25 | =SUM((D6-C6)+(F6-E6))*24 | 8.25 | 8:15 | |
| Thursday, July 12, 2018 | 9:45 AM | 1:00 PM | 2:00 PM | 6:30 PM | 7.75 | =SUM((D7-C7)+(F7-E7))*24 | 7.75 | 7:45 |
| Day | Start Time | Lunch Starts | Lunch Ends | End Time | Total Time of Work (Hrs.) | Total Time of Work (Hrs. & Mins.) | Formula |
| Sunday, July 08, 2018 | 9:00 AM | 1:00 PM | 2:00 PM | 6:00 PM | 8 | 8 Hrs. 0 Mins. | =INT(G3)&" Hrs."&" "&TEXT((G3-INT(G3))*60,"0")& " Mins." |
| Monday, July 09, 2018 | 9:15 AM | 1:15 PM | 2:00 PM | 6:15 PM | 8.25 | 8 Hrs. 15 Mins. | =INT(G4)&" Hrs."&" "&TEXT((G4-INT(G4))*60,"0")& " Mins." |
| Tuesday, July 10, 2018 | 9:00 AM | 1:15 PM | 2:15 PM | 6:30 PM | 8.5 | 8 Hrs. 30 Mins. | =INT(G5)&" Hrs."&" "&TEXT((G5-INT(G5))*60,"0")& " Mins." |
| Wednesday, July 11, 2018 | 9:30 AM | 1:45 PM | 2:45 PM | 6:45 PM | 8.25 | 8 Hrs. 15 Mins. | =INT(G6)&" Hrs."&" "&TEXT((G6-INT(G6))*60,"0")& " Mins." |
| Thursday, July 12, 2018 | 9:45 AM | 1:00 PM | 2:00 PM | 6:30 PM | 7.75 | 7 Hrs. 45 Mins. | =INT(G7)&" Hrs."&" "&TEXT((G7-INT(G7))*60,"0")& " Mins." |
no reviews yet
Please Login to review.
