How do I create a countdown formula?
Believe it or not, I like to do everything with as much difficulty as
possible. Actually, for whatever reason, I have always assumed that
NETWORKDAYS was based on a 360 day calendar. Don't know why, but I did :)
I am sure that others could possibly simplify this formula...
--
John C
"Peo Sjoblom" wrote:
I missed workdays but in that case why not use?
=NETWORKDAYS(TODAY(),DATE(2008,12,31),Holidays)
--
Regards,
Peo Sjoblom
"John C" <johnc@stateofdenial wrote in message
...
Seeing as how no one has actually given you a formula for workdays between
today and the end of the year, I will give you one. I am sure that others
could possibly simplify this formula, but this is what I came up with.
=((WEEKNUM(DATE(2008,12,31))-1)-WEEKNUM(TODAY()))*5+WEEKDAY(DATE(2008,12,31),2)+(6-WEEKDAY(TODAY(),2))-SUMPRODUCT(--(Holidays=TODAY()),--(Holidays<=DATE(2008,12,31)),(Holidays2))
If you work on weekends, it will come up with some oddball numbers, but
during the week, it should be right on :). Except on 12/31/2008, still
shows
1 more day, just like on 12/30/2008.
This also assumes that you have ranges somewhere called Holidays and
Holidays2. Holidays is just your normal list of dates that are actual
holidays, and Holidays2 is a formula that looks as follows: (assuming your
Holiday range starts in J1)
=IF(J1="","",IF(WEEKDAY(J1,2)<6,1,0))
What my formula is doing is first getting the weeknumber of the week
before
the end of the year falls, subtracting the current week number, then
multiplying this number by 5 (for 5 workdays). Then it adds the workday
number of the last week, and the current week. Then it subtracts any
holidays
that fall during the week.
Hope this helps.
--
John C
"Crystal M" wrote:
I made a worksheet of things that need to be done by the end of the year,
and
I want to do a countdown of work days left in the year. I can manually
change day-by-day when I open the spreadsheet, but sometimes I forget.
Anyone have any suggestions??
|