Cost apportionment / accrual period formula

**pfusella** · 05-18-2025, 12:38 AM

Hello!

I have a really simple one that I'm having trouble with!

All I'm looking to do is apportion a total cost to a specified period.

E.g. In the sheet there will be a row with the following data:
Col A - Amount
Col B - Period Start
Col C - Period End

E.g. I have a wage that I paid for $3269.23 that covered the 2 week period of 22-Mar-2025 to 04-Apr-2025 (period always starts on a Saturday and ends on a Friday).

I want to apportion that cost correctly to the March period. In theory it should be total cost / 14 days, multiplied by the # days that are in March.

You can see in the example sheet (Col D) a formula from the internet that I found but I don't think it's doing the correct calc. I have it laid out in the example sheet.

I just want a clean and elegant formula that works without having to do the manual calc I show above!

Surely there's an excel function that just does this for us at this point? What do all the accountants do?!

Thanks!

**alansidman** · 05-18-2025, 01:28 AM

I did it with a couple of helper columns instead of one formula.

Excel 2016 (Windows) 64 bit

	F	G	H
6	EOM Adj	Days in Currentt Month	Current Period Wages
7	=IF(E7-14<B7,E7,"")	=IF(F7="","",E7-B7)	=IFERROR((A7/14)*G7,"")
8	=IF(E8-14<B8,E8,"")	=IF(F8="","",E8-B8+1)	=IFERROR((A8/14)*G8,"")
9	=IF(E9-14<B9,E9,"")	=IF(F9="","",E9-B9+1)	=IFERROR((A9/14)*G9,"")
10	=IF(E10-14<B10,E10,"")	=IF(F10="","",E10-B10+1)	=IFERROR((A10/14)*G10,"")
11	=IF(E11-14<B11,E11,"")	=IF(F11="","",E11-B11+1)	=IFERROR((A11/14)*G11,"")
12	=IF(E12-14<B12,E12,"")	=IF(F12="","",E12-B12+1)	=IFERROR((A12/14)*G12,"")
13	=IF(E13-14<B13,E13,"")	=IF(F13="","",E13-B13+1)	=IFERROR((A13/14)*G13,"")
14	=IF(E14-14<B14,E14,"")	=IF(F14="","",E14-B14+1)	=IFERROR((A14/14)*G14,"")
15	=IF(E15-14<B15,E15,"")	=IF(F15="","",E15-B15+1)	=IFERROR((A15/14)*G15,"")
16	=IF(E16-14<B16,E16,"")	=IF(F16="","",E16-B16+1)	=IFERROR((A16/14)*G16,"")
17	=IF(E17-14<B17,E17,"")	=IF(F17="","",E17-B17+1)	=IFERROR((A17/14)*G17,"")
18	=IF(E18-14<B18,E18,"")	=IF(F18="","",E18-B18+1)	=IFERROR((A18/14)*G18,"")
19	=IF(E19-14<B19,E19,"")	=IF(F19="","",E19-B19+1)	=IFERROR((A19/14)*G19,"")
20	=IF(E20-14<B20,E20,"")	=IF(F20="","",E20-B20+1)	=IFERROR((A20/14)*G20,"")
21	=IF(E21-14<B21,E21,"")	=IF(F21="","",E21-B21+1)	=IFERROR((A21/14)*G21,"")
22	=IF(E22-14<B22,E22,"")	=IF(F22="","",E22-B22+1)	=IFERROR((A22/14)*G22,"")
23	=IF(E23-14<B23,E23,"")	=IF(F23="","",E23-B23+1)	=IFERROR((A23/14)*G23,"")
24	=IF(E24-14<B24,E24,"")	=IF(F24="","",E24-B24+1)	=IFERROR((A24/14)*G24,"")
25	=IF(E25-14<B25,E25,"")	=IF(F25="","",E25-B25+1)	=IFERROR((A25/14)*G25,"")
26	=IF(E26-14<B26,E26,"")	=IF(F26="","",E26-B26+1)	=IFERROR((A26/14)*G26,"")
27	=IF(E27-14<B27,E27,"")	=IF(F27="","",E27-B27+1)	=IFERROR((A27/14)*G27,"")
28	=IF(E28-14<B28,E28,"")	=IF(F28="","",E28-B28+1)	=IFERROR((A28/14)*G28,"")
29	=IF(E29-14<B29,E29,"")	=IF(F29="","",E29-B29)

Sheet: Example

**bebo021999** · 05-18-2025, 03:54 AM

The formula you're using is based on the data from end date - start date, with the difference being the gap between the two dates (only counting one of the start or end dates).
However, since you’ve defined the working period as starting on Saturday and ending on Friday, the difference of 7 days means 6 + 1, which implies end - start + 1.
Therefore, your formula should be:

=MAX(0, MIN($C7+1, EDATE(D$2, 1)) - MAX($B7+1, D$2) + 1) / ($C7 - $B7 + 1) * A7

**pfusella** · 05-18-2025, 03:54 AM

@alansidman

Thanks for the reply. Something may have been missed in my communication.

I'm looking at your file and for instance if we look at row 8, you've calculated wages of $934.07, but that period starts in Dec24 (Col B) and ends in Jan25 (Col C). The period we're trying to calculate for is in cell D2 & D3 (e.g. Mar25). Therefore the wages output for this row should be zero.

Does that make sense?

For example, row 14 should have an output because the period starts on 22nd Mar and ends on 4th Apr. Therefore 10 days worth of wages should be the output for this line.

**pfusella** · 05-18-2025, 04:02 AM

@bebo021999

Thanks for the reply.

I actually tried that, but the output isn't the same as calculating it manually.

E.g. let's take row 14 for example. 10 days fall in March, and the total is 2369.23 (so $233.52 daily).

If you calculate this manually, it would be 10 x 233.52 = 2,553.20. But using your calc we get $2,101.65.

Can you see what I'm talking about?

I can't for the life of me work out what's going on here!

**JeteMc** · 05-18-2025, 09:33 AM

Try pasting the following into cell D7 and then copying down:

Formula:

Please Login or Register  to view this content.

Let us know if have any questions.

**pfusella** · 05-18-2025, 07:13 PM

This worked! Thank you!

**JeteMc** · 05-18-2025, 08:51 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

**pfusella** · 05-21-2025, 03:15 AM

I have an adjustment I need in the formula!
Atm the formula works for positive amounts, but when there's a negative amount in there it outputs a completely wrong number.
Are you able to please adjust the formula so that it works for negative amounts as well as positive amounts?
Thanks!

**JeteMc** · 05-21-2025, 10:13 PM

Please provide examples.

**pfusella** · 05-21-2025, 11:59 PM

Apologies! Please see attached.

Essentially the formula won't accrue negative numbers, only positive numbers. For a negative number it just outputs zero, but I would like for it to accrue the negative number in the same way it does the positive.

Thanks!

**JeteMc** · 05-22-2025, 09:24 AM

This will yield a negative amount when dragged down from cell D7 to cell D8:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**pfusella** · 05-22-2025, 01:20 PM

Thats the one. Perfect! Thank you!

**JeteMc** · 05-22-2025, 02:14 PM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

**pfusella** · 05-22-2025, 02:51 PM

Apologies. There is still one instance in which this does not work.

Where the amount is a negative number, and the period start & end dates fall outside the accrual date, it outputs an amount when it should just be zero.

See file attached - thx!

**JeteMc** · 05-22-2025, 06:20 PM

It seems as if the period start & end dates fall outside the accrual date that both positive and negative amounts should produce an output of zero.
Put the following into cell D7 and then drag the fill handle down to cell D8:

Formula:

Please Login or Register  to view this content.

To test copy the dates from cells D11:D12 into cells D4:D5.
Let us know if you have any questions.

**pfusella** · 05-22-2025, 09:20 PM

That seems to fully work now. Thanks!

Cost apportionment / accrual period formula

LinkBack

Thread Tools

Rate This Thread

Display

Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Re: Cost apportionment / accrual period formula

Thread Information

Users Browsing this Thread

Similar Threads

VAT accrual formula is showing positive accrual on 1st day of quarter (should be negative)

total cost of membership over period at set cost increase per renewal

[SOLVED] Formula required for Cost distribution over a period for current as well as future years

Calculating rental period and cost

[SOLVED] Total Compensation (Cost Per Pay Period + Prorated)

Cost of Goods Macro for New Period

Apportionment Formula

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions