Counting Dates Including Weekends

  • Thread starter Thread starter Karen Howard
  • Start date Start date
K

Karen Howard

Hello and Happy Holidays Everyone!

I have a spreadsheet that I have three columns:

Cell A1 is 12/14/03 (Date Format)
Cell B1 is 12/26/03 (Date Format)

In Cell C1 I want the dates to calculate and tell me how
many days a project is overdue and I want it to calculate
WEEKENDS and HOLIDAYS as well. (This cell will of course
be set up as a "number" format field.)

If there a formula (since I am not famility with VBA code
or using Modules) that I can enter into Cell C1? Thanks
for any assistance with this!

--Karen
 
Thanks for replying Peo. Your formula seems simply but I
just want to make sure that the formula is using "calendar
day". Is it?? Because I also want add a column that will
also give me work days based on a 5 day calendar. Please
let me know if your formula is "calendar" based. Thanks!
 
Hi Karen!

It depends upon whether or not the dates include specific times other
than the default of 00:00

Re: =B1-A1

If B1 is 22-Dec-2002 entered using that date or as a DATE function
And A1 is 12-Dec-2002 entered using that date or as a DATE function

Then B1-A1 returns 10

If you want the count to be inclusive of 12-Dec and 22-Dec, then the
formula becomes:

=(B1-A1)+1

If you want to count *completed* 24 hour periods then you need

Where: both A1 and B1 include dates and times
=INT(B1-A1)

If you want to count completed and part completed days you need:

=INT(B1-A1)+1
OR
=ROUNDUP(B1-A1,0)

There is the DATEDIF function with the third argument "d"

=DATEDIF(A1,B1,"d")

This strips or ignores the times in any dates and thus gives you the
number of completed days ignoring times on those days. The DATEDIF
function is not the same as B1-A1 where those dates contain times.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top