Calculate elapsed calendar weeks between 2 dates

[malc1982]

Need to Calculate elapsed calendar weeks between 2 dates for a presentation

please help
it's doing my nut in

 

[Fergus]

Hi Malc,

What day of the week does your calendar week start on. How do you want to treat part weeks?

 

[malc1982]

will only be a 5 day calender week mon-fri.

Got a start date column proceeded by an end date column need to show the difference

 

[Fergus]

Monthly totals.xls
	H	I	J	K
20	End	Start	No.Weeks
21	24/02/04	31/01/04	3.43
Sheet1

But how do you want to treat part weeks?

 

[malc1982]

Best off just rounding up to 1 week duration rather than just the part period

 

[Fergus]

=ROUNDUP((H21-I21)/7,0)

 

[malc1982]

is it possible to change the decimal to show 0.1 = monday - 0.5 = friday then to 1.1 1 week and monday?

if not thanks for the swift help

 

[Fergus]

Not quite sure what you mean. What happens if your start date is a Thursday and your end date a Monday, i.e. 2 days before your "week" and one day after: - n.3 ?

 

[Fergus]

You could try : =INT(NETWORKDAYS(I21,H21)/5)&" Weeks and "&(NETWORKDAYS(I21,H21)/5-INT(NETWORKDAYS(I21,H21)/5))*5&" days"

 

[malc1982]

Thursday - Monday should be .3