workdays additions and cell number display

hi guys

how do I get cell C1 to say "0" if the dates in A1 and B1 are the same.

....But if they r not the same and B1 is a later date than A1 then I want the number of workdays between those dates to be displayed as a negative number. i..e
If

A1=20 Sept 07
B1=24Sept 07

I want cell C1 = -3 (as a negative number) (which is the number of workdays difference between those dates).

However, if

A1 = 24 Sept 07
B1 = 20 Sept 07

I want C1 = 3 (as a positive number) (which is the number of workdays difference between those dates).

This is all so that I can get a sum of all the workdays differences between a number of dates in another cell (somewhere else in the spreadsheet) to indicate whether a certain task/project is x number of days behind or ahead of schedule.

Thanks for taking time out to read this. Awaiting your kind response.

Regards

If you're counting workdays then the easiest function to use is NETWORKDAYS (which is part of Analysis ToolPak add-in unless you're using Excel 2007).

Use the formula

=IF(A1=B1,0,NETWORKDAYS(B1,A1))

to install Analysis ToolPak, Tools > addins > tick "Analysis ToolPak"

I agree with DLL but you should be wary about using that exact formula as it can lead to an inconsistency in results in that if the day is the same it returns 0 (the first part of the If), but if the days are 1 day apart eg 20/9/2007 and 21/9/2007 the formula will return 2 (as result of Networkdays).

Richard

Thanks Guys

IS there is a way around the problem of networkdays function making excel think of the extra day. this is also a problem when both dates are the same and it returns a value of 1. I can get round the problem of it return the value of one by specifying that if the dates are the same then return "0" but how can I ensure the if the dates are 1 day apart then it returns a value of 1 rather than the 2 that it does return.??

Thanks,

Perhaps:

=NETWORKDAYS(B1,A1)-SIGN(NETWORKDAYS(B1,A1))

Richard

Richard

Its working fine that way.

Many Thanks!!

Regards

sorry If I didnt clarify...

its now working the way you have suggested as:

=NETWORKDAYS(B1,A1)-SIGN(NETWORKDAYS(B1,A1))

thanks!