How do I create a countdown formula?
 

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

How do I create a countdown formula?
 

=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



"Crystal M" <Crystal wrote in message
...
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??

How do I create a countdown formula?
 

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

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

How do I create a countdown formula?
 

In an unused cell enter:

=TEXT(DATEVALUE("1/1/2009")-TODAY(),"0") & " days left in 2008"
--
Gary''s Student - gsnu200799

How do I create a countdown formula?
 

Hi

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

Regards,
Pedro J.

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

How do I create a countdown formula?
 

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

"FSt1" wrote in message
...
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

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

How do I create a countdown formula?
 

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

How do I create a countdown formula?
 

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

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

How do I create a countdown formula?
 

I do that too at times :)

--


Regards,


Peo Sjoblom

"John C" <johnc@stateofdenial wrote in message
...
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??