MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting Days


Posted by Derek Joe on February 08, 2002 11:12 AM

I need a formula that will calculate the number of days between a start date and an end date. For example, the formula would return how many days are in between 1/1/2000 and 1/10/2001. I tried the days360 function but it didn't return the correct number of days.


Posted by Aladin Akyurek on February 08, 2002 11:54 AM

=EndDate-StartDate

Replace these names by the cell refs and format the cell of the formula as General.

Posted by Todd K on February 08, 2002 2:38 PM

Hey, anyone - gotta a method to count just week or work days (Monday through Friday)? That would be a neat trick...

Posted by Aladin Akyurek on February 08, 2002 3:02 PM

=NETWORKDAYS(A1,B1)

where B1 > A1. Requires Analysis Toolpak (use Tools|Add-Ins if not available).

Posted by Todd K on February 09, 2002 1:56 PM

Thanks Aladin - here's my challenge...let's say I have date 1, and I wish to add 8 work days to it and get the correct answer (i.e., real date) at the end of things...but I never want to see a Saturday or Sunday show up in my actuals, or to be counted towards my total...any hints with this one?

e.g.
A2 = February 12, 2002
B1 = 8 work days
B2 = I want the correct date if 8 work days are added to February 12, 2002...

Posted by Mark W. on February 09, 2002 2:12 PM

=WORKDAY(A2,$B$1) ...

All of the functions mentioned above and their
descriptions can be found in the Excel Help topic
"About date and time functions". Take a look!

Posted by Todd K on February 10, 2002 1:56 PM

Re: =WORKDAY(A2,$B$1) ...

Wow, thanks for the help - you just resolved a major problem for me!!! Too bad I don't read the help file, right? :)

Posted by Martha on February 10, 2002 6:44 PM

What formula should I use to calculate a person's age from the birthdate using 365 days.

Posted by Martha on February 10, 2002 6:52 PM

Re: Calculating Age

How about a formula to calcualte an age from the birthdate. How do I used 365 days?

Posted by Aladin Akyurek on February 10, 2002 9:50 PM

Re: Calculating Age

> How about a formula to calcualte an age from the birthdate. How do I used 365 days?

=DATEDIF(A1,TODAY(),"y")&" years," &DATEDIF(A1,TODAY(),"ym")&" months, " &DATEDIF(A1,TODAY(),"md")&" days"

or

=YEARFRAC(TODAY(),A1)

where A1 houses a birthdate.