days calculation between two date : Formula Required.

**avk** · 07-22-2007, 02:48 AM

If Invoice date is : 17.06.2007 & payment due date is 02.08.2007

In above please confirm formula of days calculation between above two date.

Regards : avk

**avk** · 07-22-2007, 04:22 AM

anybody confirm per return.

**oldchippy** · 07-22-2007, 04:55 AM

If your invoice date is in A1, and your payment date in B1, then in C1

=B1-A1 format the cell to General, to include the start date as well, then

=B1-A1+1

**avk** · 07-23-2007, 01:27 AM

Sorry, the result not show. AFter input of date a1 (17.06.2007) & b1 (02.08.2007) & in c1 : write formula =B1-A1+1 (even c1 cell format into general.
The Error mark show : #value!

Please help.

**oldchippy** · 07-23-2007, 04:38 AM

Try date input as 17/06/2007 and 02/08/2007

**avk** · 07-23-2007, 07:46 AM

Sorry the result not show some problem please refer attach tmp.doc file.

**oldchippy** · 07-23-2007, 07:50 AM

Your date in the first cell is formatted to the left, this usually indicated that the cell is formatted to text and not General, Number or Date.

Try a new sheet and enter the dates again with "/" between the numbers.

**sjm** · 07-25-2007, 07:00 AM

I need to do the same sort of thing, but want to calculate how many working days are left (minus weekends). Can this be done?

Many thanks!

**oldchippy** · 07-25-2007, 08:14 AM

What you need is the NETWORKDAYS function, but to use it you will need to install the Analysis Tool Pak

http://office.microsoft.com/en-gb/ex...277241033.aspx

=NETWORKDAYS(StartDate,EndDate,Holidays)

Then with your start date in A1 and your end date in B1 and list any holidays - Xmas, Public Holidays

=NETWORKDAYS(A1,B1,Holidays) The calculation does not include the last day, to correct this add 1 to the result

=NETWORKDAYS(A1,B1,Holidays)+1

**sjm** · 07-25-2007, 11:02 AM

Thanks, this has worked to an extent, BUT between the dates 25th July 07 and 10th August 07, I get 14 days, there should only be 12 working days (2 weekends in the way)?

I have put both dates to date format 14-Mar-98 and the formula reads =NETWORKDAYS(D41,H41)+1

What am I doing wrong?

**daddylonglegs** · 07-25-2007, 11:25 AM

In fact NETWORKDAYS counts both the start date and the end date so

=NETWORKDAYS(D41,H41) where D41 is 25th July 2007 and H41 10th August 2007

should return 13

If you don't want to count the start date use

=NETWORKDAYS(D41,H41)-1

I'm assuming D41 and H41 will always be workdays.....

The format of the dates makes no difference as long as they are recognisable dates