How do I create a countdown formula?

[Crystal M]

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??

 

[Peo Sjoblom]

=DATE(2009,1,1)-TODAY()

or


=DATEDIF(TODAY(),DATE(2009,1,1),"d")



depending on how you count you might want to change this part

DATE(2009,1,1)

to

DATE(2008,12,31)



--


Regards,


Peo Sjoblom

 

[FSt1]

hi
assuming that the end of the year is 12/31/08
="12/31/2008" - Today()
this will update daily because of the today function.

regards
FSt1

 

[Gary''s Student]

In an unused cell enter:

=TEXT(DATEVALUE("1/1/2009")-TODAY(),"0") & " days left in 2008"

 

[Infinitogool]

Hi

Try
=DATE(YEAR(TODAY()),12,31)-TODAY()

Regards,
Pedro J.

 

[Peo Sjoblom]

Note that your solution is US centric and it won't for UK.


This adaptation will work in Europe as well as the US


="2008-12-31" - Today()


--


Regards,


Peo Sjoblom

 

[John C]

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.

 

[Peo Sjoblom]

I missed workdays but in that case why not use?

=NETWORKDAYS(TODAY(),DATE(2008,12,31),Holidays)

--


Regards,


Peo Sjoblom

 

[John C]

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

 

[Peo Sjoblom]

I do that too at times

--


Regards,


Peo Sjoblom