how long in each period?

jonny_monster · May 21st, 2002, 01:48 PM

I'm wondering if anybody is willing to pass on their expertise?

I work for an organisation that helps under-priveliged people start-up in business. We receive payment for this from another funding agency.

The way it works is this - we offer our services to each client for 6 months. During this time, we receive a small payment for each week a client spends running their business.

Our payment year is split into 12 periods of either four or five weeks. To receive our funding we have to send an invoice to the funding agency at the end of each period. This invoice has to state exactly how many weeks each client has been running their business during the period.

If anybody could suggest a means of getting excel to do this automatically each period based on the entrance and exit date of each client I would be most gratefull.

P.S. - A client can only enter on a Monday, meaning their are only 4 (or 5) possible start dates in each period. I have a feeling this makes the task easier.

PaddyD · May 21st, 2002, 03:15 PM

Jonny,

Welcome!
It's fairly easy to work out how many weeks there are between any particular start / end date (e.g. (end date - start date)/7 - you could round these up or down to get whole numbers of weeks.
The difficult bit is to determine how many of those weeks fall within your billing periods. I.E. for any end date, you'd need to know (at least) the date of the previous billing period.
How exactly are the dates of your billing periods calculated?

Paddy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 01:48 PM	#1
jonny_monster New Member Join Date: May 2002 Posts: 1	I'm wondering if anybody is willing to pass on their expertise? I work for an organisation that helps under-priveliged people start-up in business. We receive payment for this from another funding agency. The way it works is this - we offer our services to each client for 6 months. During this time, we receive a small payment for each week a client spends running their business. Our payment year is split into 12 periods of either four or five weeks. To receive our funding we have to send an invoice to the funding agency at the end of each period. This invoice has to state exactly how many weeks each client has been running their business during the period. If anybody could suggest a means of getting excel to do this automatically each period based on the entrance and exit date of each client I would be most gratefull. P.S. - A client can only enter on a Monday, meaning their are only 4 (or 5) possible start dates in each period. I have a feeling this makes the task easier.

May 21st, 2002, 03:15 PM	#2
PaddyD MrExcel MVP Join Date: May 2002 Posts: 13,278	Jonny, Welcome! It's fairly easy to work out how many weeks there are between any particular start / end date (e.g. (end date - start date)/7 - you could round these up or down to get whole numbers of weeks. The difficult bit is to determine how many of those weeks fall within your billing periods. I.E. for any end date, you'd need to know (at least) the date of the previous billing period. How exactly are the dates of your billing periods calculated? Paddy