prorata value based

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pro-rata value based on 2 dates

Pro-rata value based on 2 dates

resolvedResolved · Low Priority · Version 2016

Jenna has attended:
Excel Intermediate course

Pro-rata value based on 2 dates

Hello, I am working on P11D's and have been given a cost for the year which I am having to pro-rate based on employees dates that they have joined/left the schemes in the year.
So for example, joined 04/05/2019 left 12/12/2019 full year cost is £200 but only to be charged for those dates
Is there an easy way to find the pro-rata using excel please?

Many thanks in advance!
Jenna

RE: Pro-rata value based on 2 dates

Hi Jenna,

The question is clear however just need the following answered

Are they only being charged for the working days in that period or the total amount of days passed between those two dates?

RE: Pro-rata value based on 2 dates

Hi Ron,

It would be the total number of days between the dates please.

Many thanks,
Jenna

RE: Pro-rata value based on 2 dates

Thank you Jenna, Let me have a little play and I will get back to you asap

Enjoy the sunshine

Ron

RE: Pro-rata value based on 2 dates

Thank you for your question posted in STL's forum Jenna.

You want to calculate a pro rata charge based on a start and finish date

The first thing to do is to determine the daily rate for the year.
If the yearly rate is 200 then the daily rate would be 200/365.

The next thing to determine is the amount of days that have expired between the start date and the finish date that the employee was assigned to the scheme. To determine that you can simply subtract the start date from the finish date:

[=Cell containing the finish Date - Cell containing the start date]

You can now multiply the number of days expired and the daily rate which should now give you the total rate for the period.

I have attached an Excel workbook with 2 solutions:
Solution1 has the different calculations in separate columns to get to the end result

Solution2 Has the pro rata calculation in one cell by combining the different steps into one calculation.

I hope this helps you.


Attached files...

Example.xlsx

RE: Pro-rata value based on 2 dates

Worked perfect thank you Ron!

RE: Pro-rata value based on 2 dates

You are very welcome Jenna. I am glad it worked

Kind regards

Ron


 

Excel tip:

Select only cell that contain text to lock format

For selecting cells that only contain Text in Excel

By selecting cells that only contain text, you can delete, fill or protect cells of this type.

Use short cut to Go to box (F5) or Edit, Go to
In the dialog box, click special button & select Constants and only check text or any other desired type.

Click OK.

And text cells will all be highlighted for you to apply format. Please note only works on one sheet at a time.

View all Excel hints and tips


Server loaded in 0.11 secs.