Prorated salary, incomplete month

**Ginger777** · 01-14-2025, 11:42 AM

For example: monthly salary of an employee A is $30,000
$35,000 effective from 2 Jan 2024
$40,000 effective from 15 Apr 2024
In data file, there are 2 rows for Employee A
Fiscal year is 1 Jan 2024 to 31 Dec 2024

Question: How to calculate the total salary in 2024?

I tried to use (days(end date, start date)+1)/366, however the salary is not accurate because what I need is ((30/31+2+14/30)*35000)+(16/30+8)*40000

Please advise, thanks

**AliGW** · 01-14-2025, 11:44 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**FlameRetired** · 01-14-2025, 07:49 PM

Please do upload a workbook as AliGW requests.
In the meantime it sounds like this will do what you expect. In D2 this formula.

Formula:

Please Login or Register  to view this content.

	A	B	C	D
1	Change dates	Amounts	Daily amounts
2	1/1/2024	$30,000.00	$967.74	$466,344.09
3	1/2/2024	$35,000.00	$1,129.03
4	4/15/2024	$40,000.00	$1,333.33
5	12/31/2024	$40,000.00	$1,290.32

**FlameRetired** · 01-15-2025, 05:54 PM

This is better. Please see attached.
In column A the dates specified in post #1 are forced in cells A2, A3 and A7. Then this formula in A4 and copied down to A6 fill in standardized dates.

Formula:

Please Login or Register  to view this content.

. Then copy A6 and paste into A8:A16. The monthly salaries are forced in column B.

This formula in C2 and copied down to specify how many days in relative months column A

Formula:

Please Login or Register  to view this content.

In column D this calculates what each day's salary is in relation to the changing amounts in column B.

Formula:

Please Login or Register  to view this content.

In column E the amounts for each date span.

Formula:

Please Login or Register  to view this content.

Then the year's total in F2.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F
1	Change dates	Monthly increases	Days in whole month	Daily ammounts	Amount to date	End of year prorated
2	1/1/2024	$30,000.00	31	$967.74	$967.74	$463,870.97
3	1/2/2024	$35,000.00	31	$1,129.03	$1,129.03
4	1/31/2024		31	$1,129.03	$32,741.94
5	2/29/2024		29	$1,206.90	$35,000.00
6	3/31/2024		31	$1,129.03	$35,000.00
7	4/15/2024	$40,000.00	30	$1,333.33	$20,000.00
8	4/30/2024		30	$1,333.33	$20,000.00
9	5/31/2024		31	$1,290.32	$40,000.00
10	6/30/2024		30	$1,333.33	$40,000.00
11	7/31/2024		31	$1,290.32	$40,000.00
12	8/31/2024		31	$1,290.32	$40,000.00
13	9/30/2024		30	$1,333.33	$40,000.00
14	10/31/2024		31	$1,290.32	$40,000.00
15	11/30/2024		30	$1,333.33	$40,000.00
16	12/31/2024		31	$1,290.32	$40,000.00