Salary calculator in monthly calendar

1

12onetwo

New Member
Joined
Dec 22, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I'd like to keep tabs on what I make. I'm working part time as an hourly employee. I'd like to just have a monthly calendar in excel for each month and just input when I come and leave, and have it calculate the earnings that day and tally it up at the end of the month. The thing is I'd need it to also recognize that there are different pay for different hours. Mon-Fri 6-18 is normal (11,61€), Mon-Fri 18-22 is 1,15x, any day 22-06 is 2x, Saturday 6-18 is 1,2x, Saturday 18-> is 2x, and Sunday is 2x. Also some days are 2x (Christmas Eve, Easter, Midsummer eve). So if I type 15-23 (or if there are two input fields for start and end time) it would know based on the day what it would have to apply (15-18 1x, 18-22 1.15x, 22-23 2x of Mon-Fri).
I've tried to look for templates but can't find a one that would fit my needs, only the one monthly calendar. Could I find some help here?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
This is one possible Solution:
Test_Input_Output.xlsx
ABCDEFGHIJKLMNOP
1Time for Different Working ShiftHour PayPay for each Shift
2Input TimeOutput timeTotal HoursDays6-1818-2222-611.616-1818-2222-6Total Pay
3Saturday, December 26, 2020 8:03:18Saturday, December 26, 2020 18:03:1810:00:0009:56:420:03:180:00:006.75230.92291.27426200102044.47798
4Sunday, December 27, 2020 18:03:18Monday, December 28, 2020 23:25:1829:22:00112:00:007:56:429:25:187.98278.64184.48299.115462
5Monday, December 28, 2020 21:03:18Tuesday, December 29, 2020 8:03:1811:00:0012:03:183:03:185:53:241.3423.8585570.93715.698575
6Tuesday, December 29, 2020 15:03:18Wednesday, December 30, 2020 12:03:1821:00:0019:00:004:00:008:00:002.50104.4992.887.74
7Wednesday, December 30, 2020 8:03:18Wednesday, December 30, 2020 18:03:1810:00:0009:56:420:03:180:00:003.75115.46151.2742620010
8Thursday, December 31, 2020 18:03:18Thursday, December 31, 2020 23:25:185:22:0000:00:003:56:421:25:184.98052.67166751.375462
9Friday, January 01, 2021 21:03:18Saturday, January 02, 2021 12:03:1815:00:0016:03:183:03:185:53:245.5070.2985570.93715.698575
10Saturday, January 02, 2021 15:03:18Sunday, January 03, 2021 18:03:1827:00:00114:56:424:03:188:00:006.75347.022994.1542627.74
11Sunday, January 03, 2021 18:03:18Sunday, January 03, 2021 18:03:180:00:0000:00:000:00:000:00:007.75000
12Monday, January 04, 2021 18:03:18Monday, January 04, 2021 18:03:180:00:0000:00:000:00:000:00:001.75000
13Tuesday, January 05, 2021 18:03:18Tuesday, January 05, 2021 18:03:180:00:0000:00:000:00:000:00:002.75000
14Wednesday, January 06, 2021 18:03:18Wednesday, January 06, 2021 18:03:180:00:0000:00:000:00:000:00:003.75000
15Thursday, January 07, 2021 18:03:18Thursday, January 07, 2021 18:03:180:00:0000:00:000:00:000:00:004.75000
16Friday, January 08, 2021 18:03:18Friday, January 08, 2021 18:03:180:00:0000:00:000:00:000:00:005.75000
17Friday, January 08, 2021 18:03:18Saturday, January 09, 2021 18:03:1824:00:00112:00:004:00:008:00:005.75167.18492.887.74
18Sunday, January 10, 2021 18:03:18Sunday, January 10, 2021 18:03:180:00:0000:00:000:00:000:00:007.75000
19Monday, January 11, 2021 18:03:18Monday, January 11, 2021 18:03:180:00:0000:00:000:00:000:00:001.75000
20Tuesday, January 12, 2021 18:03:18Tuesday, January 12, 2021 18:03:180:00:0000:00:000:00:000:00:002.75000
21Wednesday, January 13, 2021 18:03:18Wednesday, January 13, 2021 18:03:180:00:0000:00:000:00:000:00:003.75000
22Thursday, January 14, 2021 18:03:18Thursday, January 14, 2021 18:03:180:00:0000:00:000:00:000:00:004.75000
Sheet1
Cell Formulas
RangeFormula
D3:D22D3=C3-B3
E3:E22E3=DAYS(C3,B3)
F3:F22F3=IF(AND(MOD(C3,1)<0.75,MOD(B3,1)<0.25),0.5*DAYS(C3,B3)+0.75-MAX(MOD(B3,1),0.75)+MAX(MOD(C3,1),0.25)-0.25,IF(MOD(B3,1)>MOD(C3,1),0.75-MIN(MOD(B3,1),0.75)+MAX(MOD(C3,1),0.25)-0.25,DAYS(C3,B3)*0.5+IF(AND(MOD(B3,1)>0.75,MOD(C3,1)>0.75),0,MIN(MOD(C3,1),0.75)-IF(MOD(B3,1)>0.75,0.25,MAX(MOD(B3,1),0.25)))))
G3:G22G3=IF(AND(MOD(C3,1)<22/24,MOD(B3,1)<0.75),(22/24-0.75)*DAYS(C3,B3)+22/24-MAX(MOD(B3,1),22/24)+MAX(MOD(C3,1),0.75)-0.75,IF(MOD(B3,1)>MOD(C3,1),MIN(MOD(B3,1),22/24)-MAX(MOD(C3,1),0.75),(22/24-0.75)*DAYS(C3,B3)+IF(AND(MOD(B3,1)>22/24,MOD(C3,1)>22/24),0,MIN(MOD(C3,1),22/24)-IF(MOD(B3,1)>22/24,0.75,MAX(MOD(B3,1),0.75)))))
H3:H22H3=D3-(F3+G3)
J3:J22J3=WEEKDAY(B3,2)+MOD(C3,1)
K3:K22K3=IF(J3<=5.75,F3*24*$I$2,IF(J3<=6.75,F3*24*$I$2*1.2,F3*24*$I$2*2))
L3:L22L3=IF(K3<=(5+22/24),G3*24*$I$2*1.15,G3*24*$I$2*2)
M3:M22M3=H3*$I$2*2
O3O3=SUM(K3:M22)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
1K
felixstraube
F
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
453
Legacy 352679
L
J
  • Solved
Formula Filled Dates
Replies
4
Views
350
Fluff
Fluff
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
774
felixstraube
F
F
  • Solved
Calculate UK Bank Holidays
Replies
2
Views
1K
fintail99
F

Forum statistics

Threads
1,215,710
Messages
6,126,396
Members
449,312
Latest member
sweetfriend9

Share this page

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
Back
Top