Using NetworkDays Formula

[woodland81]

In my Spreadsheet i have got First Day of Absence and Last Day of Absense Dates. Next column is Amount of Weekdays Absent using these to days and the formula =NETWORKDAYS(H6,I6)

If someone is currently off sick the Last DAy is completed. how can I stop a crazy Minus number appearing in the Total amount of Weekdays Absent?

 

[p45cal]

If a person is off for 1 day only, is their
First Day of Absence date
and
Last Day of Absence date
the same?

 

[woodland81]

yes that is correct if the first day and last day are the same day, it still counts as 1day sickness

 

[p45cal]

If one of the dates is blank, you're likely to see negative numbers so:
=IF(COUNTBLANK(H6:I6)=0,NETWORKDAYS(H6,I6),"")
You'll also probably need to add 1 to a networkday calculation so that the same date for 1st and last date results in 1

 

[barry houdini]

You could count the days up to the current day if you use

=NETWORKDAYS(H6,IF(I6="",TODAY(),I6))

or if you just want a blank result until I6 is filled in then use

=IF(I6="","",NETWORKDAYS(H6,I6))

 

[woodland81]

Thats Great those options, in the beginning I did think i needed some reference to Today. you guys have come up with both

I have another column called Currently Sick, this just has a manual 'Y' entered if thats the case.

I was thinking of having some formula to check IF Last Day of Absence (I6)had an entry, if it didnt meant a 'Y' was to be put in (N6). Is this easily done, through validation of some sort?

Lee