working with dates

S

Svetlana

Good afternoon
i am really need your help. i have got invoices here to work with and i need
to find out how it was calculated.
company charges me £70p/w hire from 01/01/06 to 31/01/06 and amount is
£303.33. how would i set formula to calculate how many days are in a sertain
period of time and how many days if week is not complete?
Thank you very much
Svetlana
 
R

Roger Govier

Hi Svetlana

I think you might be better off working in days, rather than trying to
calculate weeks and days and then using fractions of weeks.

To calculate the interval between 2 date, take the earliest date away
from the latest date and add 1 (if you want to include the whole of the
start date and the whole of the end date)

With start date in A1, End Date in B1 enter in C1
=B1-A1+1 which would give 31 days

If they are charging £70 per week, it sounds as though they are basing
it on a 7 day week at £10 per day.
In which case, they charged you for 30.33 days out of the 31 days in the
period concerned.

If the basis of charging is Working days (5?) then if you have the
Analysis Toolpak loaded
Tools>Addins>tick Analysis Toolpak
Then you can use the NETWORKDAYS() function. In this case you need to
use the startdate first, THEN the end date.
=(Startdate,Enddate,Holidays)
=NETWORKDAYS(A1,B1,holidays) where holidays is a named range containing
the list of public holidays
or
=NETWORKDAYS(A1,B1,$C$1:$C$9) where $C$1:$C$9 is the range of cells
where you have entered public holidays.

Note you don't have to include holidays in the formula at all if you
don't want to.
 
Top