Timesheet Calculation

sammipd

Board Regular
Joined
Jun 6, 2010
Messages
67
How can I complete this timesheet? I need Total Time worked (End Time less Start Time less Break) and Total $ Due. I've tried it with the Break as minutes or hours or x24, formatted as h:mm or 0.00. I just can't get there. Can you help?

DateStart TimeEnd TimeBreak (Minutes)Break (Hour)Total Time workedTotal Due ($25/hr)
1/1/2021​
8:00 AM​
4:30 PM​
0.00​
0.00​
1/2/2021​
9:00 AM​
5:30 PM​
30.00​
0.50​
1/3/2021​
9:00 AM​
6:30 PM​
45.00​
0.75​
1/4/2021​
10:00 AM​
7:30 PM​
60.00​
1.00​
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,573
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Date and Time.xlsm
ABCDEF
1DateStartEndBreakTotalAmount
21-Jan-218:00 AM4:30 PM08:30212.50
32-Jan-219:00 AM5:30 PM0:308:00200.00
43-Jan-219:00 AM6:30 PM0:458:45218.75
54-Jan-2110:00 AM7:30 PM1:008:30212.50
18b
Cell Formulas
RangeFormula
E2:E5E2=C2-B2-D2
F2:F5F2=E2*24*25
 
Solution

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Here's another approach. Format column E as Custom H:MM and use this formula:
=TEXT(C2-B2,"H:MM")-TIME(0,D2,0)
For column E, format it as currency with the formula:
=+E2*24*25
 

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
185
Office Version
  1. 2013
Platform
  1. Windows
Do you want like this..?

Book3
ABCDEF
1DateStart TimeEnd TimeBreak (Minutes)Total Time workedTotal Due ($25/hr)
21/1/20218:004:30 PM0:008:30212.5
31/2/20219:005:30 PM0:308:00200
41/3/20219:006:30 PM0:458:45218.75
51/4/202110:007:30 PM1:008:30212.5
Sheet1
Cell Formulas
RangeFormula
E2:E5E2=C2-B2-D2
F2:F5F2=E2*25*24
 

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
185
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Date and Time.xlsm
ABCDEF
1DateStartEndBreakTotalAmount
21-Jan-218:00 AM4:30 PM08:30212.50
32-Jan-219:00 AM5:30 PM0:308:00200.00
43-Jan-219:00 AM6:30 PM0:458:45218.75
54-Jan-2110:00 AM7:30 PM1:008:30212.50
18b
Cell Formulas
RangeFormula
E2:E5E2=C2-B2-D2
F2:F5F2=E2*24*25
Sorry I was trying to solve it while keep this thread opened and when I replied your reply was there already.

Is there any way to delete reply?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,573
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I do not believe that one can delete the message.

There is no need to be sorry. You provided a solution and the original poster should thank you.
 

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
185
Office Version
  1. 2013
Platform
  1. Windows
I do not believe that one can delete the message.

There is no need to be sorry. You provided a solution and the original poster should thank you.
Thank you!
I do not believe that one can delete the message.

There is no need to be sorry. You provided a solution and the original poster should thank you.
Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top