Results 1 to 6 of 6
Thread: Workday question again!! (2003)

20050908, 07:26 #1
 Join Date
 Jan 2004
 Location
 Norfolk, England
 Posts
 744
 Thanks
 0
 Thanked 0 Times in 0 Posts
Workday question again!! (2003)
Link corrected by HansV
Hi,
If I have a start date and an end date, can I work out how to calculate the working days (Monday to Friday) in each month.
So for instance:
Start date is today:
End date is 18/4/2006.
I need to know how many days in workdays in September are remaining.
How many in October, November and so on until 18/4/2006.
I've looked http://www.cpearson.com/excel/distribdates.htm which gives me the formula I need, but my problem lays that the start date and end date are not fixed for various reasons. Start date is always today, and end date is based the amount of days we can employee someone for.
I need a list of months starting from today up to end date displaying the working days. Today is obviously todays date, but in another month, that will be different and in theory the end date should be the same as it is today, unless the budget increases, or other factors. I hope that makes sense!!!

20050908, 07:32 #2
 Join Date
 Jan 2004
 Location
 Norfolk, England
 Posts
 744
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Workday question again!! (2003)
Think I've worked this out using:
=EOMONTH(H5,0)+1
This takes me to the last date of the month, and by adding 1, takes me to the first day of the next month!
So by using today as a start date and the above formula in the next cell I can get a rolling list of months that are relevant.
By using the formula in the previous link, this does what I need.
Finally managed to work something out by myself?

20050908, 07:40 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Workday question again!! (2003)
See attached spreadsheet. It uses the formula from Chip Pearson's example, but the series of months is adjusted so that it starts at the current month.
In the demo, the end date has been typed in, but it can be the result of a formula.
Added: I see you solved it yourself. Great!

20050908, 08:02 #4
 Join Date
 Jan 2004
 Location
 Norfolk, England
 Posts
 744
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Workday question again!! (2003)
I thought I had!
What I'm trying to do is get the figure in J2 to match with the figure in B5.
I'm guessing the anomaly is because the last week isn't a whole week and thats why they don't match.
I'm also attempting to get precise hours of the last week.
So currently there is 0.45 of a week. In hours this is 16.65 hours (37/100*45). The 0.65 in minutes is 39 minutes (60/100*65). So 16hrs 39 mins.
It would then make sense to divide this by B14. so that two days at roughly 8 hrs 20 mins each use all the budget. (I'm pretty sure this is right)
But I still can't work out how to match J2 to B5.

20050908, 10:24 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Workday question again!! (2003)
Your calculation of hourly rate incl. VAT was incorrect. To add 17.5% to an amount, you must multiply it with 1+17.5% or with 1.175 or with 117.5% (all are different ways of stating the same).
See attached version for a calculation of the amount of time/money that remains for the last day.

20050909, 06:56 #6
 Join Date
 Jan 2004
 Location
 Norfolk, England
 Posts
 744
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Workday question again!! (2003)
Thanks again, this does precisely what I was after. Also thanks for pointing out the VAT error, in my inexperience I thought that by simply adding the value in percentage would work, but after you corrected it, it seems blatantly obvious!!! Live and learn I suppose!
Thanks again!!