Network days in a calculator

[wrightyrx7]

Hi all,

I have a calculator i want to build in excel but what i want it to do is the following:-

It will have a "Start Date", "End Date", "Annual Salary", "Monthly Salary" - this data will be input manually.

Pay for a "full month" is Annual Salary / 12

Pay for part month is "monthly salary" / total network/working days in the given month * number of network/working days from date given to the end of the month,

E.g "20/11/2012" - "10/01/2013", "£365", "£30.42"

First part of example would be to calculate pay for November 12.
£30.42 / 22 (network days in November) * 9 (network days from 20/11/2012 - 30/11/2012) = £12.44

Second part of example add salary for FULL MONTHS
In example there is only 1 full month - December 12 = £30.42

Third part of example would be to calculate pay for January 13
£30.42 / 23 (network days in January) * 8 (network days between 01/01/2013 - 10/01/2013) = £10.58

So overall the Total pay would be £12.44 + £30.42 + £10.58 = £53.44


Hope this makes sense. Can anyone please help?

Regards
Chris

[Bob Phillips]

days_in_start_month: =NETWORKDAYS(start_date-DAY(start_date)+1,DATE(YEAR(start_date),MONTH(start_date)+1,0))
paid_days_in_start_month: =NETWORKDAYS(start_date,DATE(YEAR(start_date),MONTH(start_date)+1,0))

days_in_end_month: =NETWORKDAYS(end_date-DAY(end_date)+1,DATE(YEAR(end_date),MONTH(end_date)+1,0))
paid_days_in_end_month: =NETWORKDAYS(DATE(YEAR(end_date),MONTH(end_date),1),end_date)

num_full_months: =YEAR(end_date)*12+MONTH(end_date)-(YEAR(start_date)*12+MONTH(start_date))-1

monthly_pay: =ROUND(annual_pay/12,2)
pay_in_start_month: =ROUND(monthly_pay*paid_days_in_start_month/days_in_start_month,2)
pay_in_end_month: =ROUND(monthly_pay *paid_days_in_end_month/days_in_end_month,2)
total_pay: =pay_in_start_month+(monthly_pay*num_full_months)+pay_in_end_month

[Bob Phillips]

Or simply

=ROUND(ROUND(annual_pay/12,2)*NETWORKDAYS(start_date,DATE(YEAR(start_date),MONTH(start_date)+1,0))
                                                                        /NETWORKDAYS(start_date-DAY(start_date)+1,DATE(YEAR(start_date),MONTH(start_date)+1,0)),2)
+(ROUND(annual_pay/12,2)*(YEAR(end_date)*12+MONTH(end_date)-(YEAR(start_date)*12+MONTH(start_date))-1))
+ROUND(ROUND(annual_pay/12,2) *NETWORKDAYS(DATE(YEAR(end_date),MONTH(end_date),1),end_date)
                                                                         /NETWORKDAYS(end_date-DAY(end_date)+1,DATE(YEAR(end_date),MONTH(end_date)+1,0)),2)

[wrightyrx7]

Hi xld,

Many thanks for your reply.

I have tried your first one and it seems to work but not fully how i imagined.

Using Test dates
Start = 01/01/2012
End = 31/12/2012

It gives me:
Start Paid days = 22
Full months = 10
End Paid days = 21

In theory this is correct. But seeing as though the start and end date are really two more full months. Can i get this to just come out as:

Start Paid days = 0
Full months = 12
End Paid days = 0

Your formulas work perfect if the dates fall mid month but we do get some like the example in this post.


Thanks again
Chris