# excel formula

Roland Butter 19:59 27 Jun 2003
Locked

can anyone tell me the formula for calculating the number of days (including sats & suns) between two different dates?

seedie 20:05 27 Jun 2003

Dates are stored as a number. Therefore the diff must be the larger less the smaller.

CD

Roland Butter 20:11 27 Jun 2003

Thanks seedie, I was thinking in terms of calculators. eg the calculator for sum of the difference between two dates NOT including the weekends is "=NETWOKDAYS().
I am looking for the equivalent calulator that includes the sats & suns.

hellred 20:29 27 Jun 2003

=NETWORKDAYS(C4,D4)

hellred 20:31 27 Jun 2003

Start Date End Date Work Days
01-Mar-98 07-Mar-98 #NAME? =NETWORKDAYS(C4,D4)
25-Apr-98 30-Jul-98 #NAME? =NETWORKDAYS(C5,D5)
24-Dec-98 05-Jan-99 #NAME? =NETWORKDAYS(C6,D6)

What Does It Do?
This function will calculate the number of working days between two dates.
It will exclude weekends and any holidays.

Syntax
=NETWORKDAYS(StartDate,EndDate,Holidays)
Holidays : This is a list of dates which will be excluded from the calculation, such as Xmas
and Bank holidays.

Roland Butter 21:34 27 Jun 2003

Thanks hellred. I have that one. I need the calc that includes the weekends & hols.

VoG II 21:37 27 Jun 2003

You just need

=C4-C3

and make sure that you format the answer cell as Date.

Roland Butter 22:03 27 Jun 2003

Thanks VoG. You are right, it does just need simple operators. The display cell cannot be a date format though - gen. or numbers will work.

VoG II 22:15 27 Jun 2003

Yep; until you arrive at an answer that is greater than 24h. in which case it will go pear shaped. In which case custom format the cell as

[h]:mm:ss

GlasgowMary 22:44 28 Jun 2003

You can use the datedif formula. You need to put the years, months and days in different cells:

syntax: =datedif(startdatecell,enddatecell,"y")

Eg =datedif(A4,A5,"y")

Years = "y" - as above
Months = "ym" - datedif(A4,A5,"ym")
Days = "yd" - datedif(A4,A5,"yd")

I got this formula, years ago, from the PC Advisor mag Help section. Apparently it wasn't documented by Microsoft.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites