Need help with a formula

[cygmark]

Hello Guys im new to the forum,

i have a tricky question, I need a formula for my excel sheet. What im trying to create is a formula to count the days since a lost time accident, so an equation from 1 date to another....simple right.....except...i want it to count monday-saturday and one equation to count only mon-fri...


can this be done...???

any help would be greatly appericiated.

thanks Josh

 

[Jose_Chasez]

One approach would be to count all the calendar days between the two dates and then remove all of the Saturdays and/or Sundays from the daycount.

You can use Excel's WEEKDAY function to help. It will return the number 7 for Saturdays and 1 for Sundays.

 

[Scott Huish]

For the Monday-Friday, use NETWORKDAYS (prior to XL2007, you need to load the Analysis Toolpak for this.

Excluding weekends with WEEKDAY, I would use the optional parameter 2, which returns 6 for Saturday and 7 for Sunday, then checks only need to be for >5 for weekends.

 

[cygmark]

ok, i forgot to say im a noob when it comes to excel, and i am using excel 2010. So if you guys can explain it a little easier i owe ya big....
thanks

 

[Scott Huish]

NETWORKDAYS

Syntax is:

=NETWORKDAYS(startdate,enddate,holidays)

The third parameter holidays is optional and usually consist of range somewhere on your spreadsheet for holidays you wish to exclude from the count:

Excel Workbook
	A	B
1	9/1/2011	9
Sheet1

 

[cygmark]

the first date is:1/06/09
and the second is:7/28/10

and i got direction from my boss that the count only needs to be Monday -Friday

 

[Scott Huish]

That's really easy then:

Excel Workbook
	A	B	C
1	1/6/2009	7/28/2010	407
Sheet1

 

[cygmark]

Oh sorry the first is seperate so 1/06/09 to todays date> exclude sat,sun
Then the second 7/28/10 to todays date>exclude sat,sun


sorry for the miss comm...

 

[cygmark]

So would it look like this then: =NETWORKDAYS(A1,TODAY)

 

[cygmark]

by the way thanks HOTPEPPER for all your help