Calculating Weekdays...ideas?

  • Thread starter Thread starter Darin
  • Start date Start date
D

Darin

Greetings,

I'm wanting to compute the number of days that have
already PASSED in a month vs. the nuber of days LEFT in a
month. Just to make it trickier, I don't want to count
Saturday's or Sunday's. So, here's what I'm looking
for...

Today is 1/19/03 now()

Therefore, if I look at a calendar, 13 WEEKDAYS have
passed INCLUDING today (this DOES NOT include Saturday or
Sunday).

There are 9 Weekday left (NOT including today).

Can anyone think of a function that would give me the
number of weekdays past (including today) in one cell,
and the number of weekdays left in another cell?

THANKS!
-Darin
 
OK, I just discovered the NETWORKDAYS function, which
pretty much does most of what I need...HOWEVER:

Does anyone know how to calculate the first day of the
month and the last day of the month based on now() ?

Like, for January, the first day would be 1-1-04, the
last day would be 1-31-04.

However, in February, it will be 2-1-04 and 2-29-04...

Any ideas?

Thanks!
 
Darin,

Days past

=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY())

Days forward

=NETWORKDAYS(TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))-1

This assumes that today goes into the days past. If you want it in days to
go, use

=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY())-1
=NETWORKDAYS(TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

NETWORKDAYS is part of the Analyis Toolpak addin, so it has to be installed

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Darin!

Take a look at the Analysis ToolPak NETWORKDAYS function

Where A1 contains 19-Jan-2004
=NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),A1)
Returns 13
=EOMONTH(A1,0)
Returns 31-Jan-2004
So:
=NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),EOMONTH(A1,0))-(NETWORKDAYS(DA
TE(YEAR(A1),MONTH(A1),1),A1))
Returns 9

Note that you can adapt to exclude holidays by giving a range
containing holidays to third argument of NETWORKDAYS

If you use these formulas and they return #NAME!, then you need to
install and select Analysis ToolPak as an Addin.
--
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.
 
Hi!

First DoM based on Now() in A1

=DATE(YEAR(A1),MONTH(A1),1)

Last DoM based on Now() in A1

=EOMONTH(A1,0)

EOMONTH is an Analysis ToolPak function but without EOMONTH you can
use:

=DATE(YEAR(A1),MONTH(A1)+1,0)
Note that Excel regards the 0th day of a month as being the last day
of the preceding month.

--
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
 
Does anyone know how to calculate the first day of the
month and the last day of the month based on now() ?
Click to expand...

As a "purist", if I were just interested in dates, I would use the TODAY()
function.

In any event:

First of Month: =TODAY() - DAY(TODAY()) + 1
End of Month: =EOMONTH(TODAY(),0)

Eomonth requires the Analysis Tool Pack to be installed. If you prefer not to
have this, you can use this formula instead:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)




--ron
 
Hi Ron!

Agreed. In any event, NETWORKDAYS, WORKDAYS and DATEDIF all truncate
the non integer part of date serial numbers.

--
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.
 
Hi Ron!

And it's not just being a purist! Daily rate maths can really get
screwed up if you use NOW() instead of TODAY().

=(NOW()-DATE(2004,1,12))*100
Returns: 869.9
(Depending upon date and time of day)

=(TODAY()-DATE(2004,1,12))*100
Returns: 800
For the same date.

NOW() should only be used if you want time of day to be used in the
calculation.
--
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.
 
Thanks EVERYONE!

I used this formula...I'm a sucker for the "one cell"
solution.

Thanks again SO much!
-Darin
 
Back
Top