Calculating Weekdays...ideas?

[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

 

[Guest]

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!

 

[Bob Phillips]

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)

 

[Norman Harker]

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.

 

[Norman Harker]

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

 

[Ron Rosenfeld]

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

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

 

[Norman Harker]

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.

 

[Norman Harker]

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.

 

[Darin Spence]

Thanks EVERYONE!

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

Thanks again SO much!
-Darin