If you have a date in A1 cell you can view the number with =N(A1). Also you
can see the number by simply pressing Ctrl+Accent grave(`), the key above
the tab key. This will also show you all the formulas on your worksheet.
Just press Ctrl+Accent grave (`) again to return to normal view. As for
dates prior to January 1, 1900 go to John Walkenbach's web site
http://j-walk.com/ss. He has an add-in for Excel called Power Utility Pack.
Part of that add-in can handle dates going back to the year 100. The
utilities cost $40. But if you buy his books, Excel 2007 Bible, Excel 2003
Bible for example, $40, the books have a coupon that gets you the utilities
for $10 and the VBA source code for an additional $20. You should be aware
that Excel has a bug in its dates. Excel will tell you that there was a
February 29, 1900. There was not. End-of-century years such as 1700, 1800,
1900, 2000 etc. although evenly divisible by 4 are not leap years unless
they are evenly divisible by 400 - so 1700, 1800, 1900 are not leap years
but 2000 is. Centuries actually begin with the year 1, such as 2001 the
first year of the 21st century. 2000 was the last year of the 20th century.
But this somehow got lost in the shuffle. The bug of February 29, 1900
started in Lotus 123 and Microsoft decided to retain the bug so that people
could convert their Lotus spreadsheets to Excel. As for date computations,
Excel has a few functions such as DATE, DATEVALUE, DAY, DAYS360, EDATE*,
EOMONTH*, MONTH, NETWORKDAYS*, NOW, TODAY, WEEKDAY, WEEKNUM*, WORKDAY*, YEAR
and YEARFRAC*. The * means that in versions of Excel prior to Excel 2007,
the Analysis Toolpak must be installed. Excel 2007 has all of the functions.
You might also note that time is stored as a fraction of 24 hours along with
the date. 1 second is 1/(24*60*60), 1 minute is 1/(24*60), one hour is 1/24.
Thus 12 AM is 0/24 = 0.0, 3 AM is 3/24 = 0.125, 12 PM is 12/24 = 0.5. March
8, 2008 is day 39515 and March 8, 2008 12 PM is 39515.5. Just as an aside,
when Pope Gregory developed the Gregorian calendar, he dropped 11 days from
the Julian calendar in the 1500's. This change took effect in England and
the U.S. in 1752. Feb 11, 1752 was followed by Feb 22. There will be an
additional day dropped from the calendar in the 3400's. Excel will almost
certainly not take this into account. So much for having dates expressed as
numbers. Excel's date range is Jan 1, 1900 - day 1 through Dec 31, 9999 -
day 2,958,465.

Tyro

On Mar 7, 3:03 pm, "Tyro" <T...@hotmail.com> wrote:

Thank you for the info on dates and how Excel views them.  There must
be a very simple way to format a cell to tell what the number of the
current day is and does it take into account leap years.  What if you
were wanting to count dates from pre 1900?

As to the formula given by Sandy and Ken I have tested it out and it
does seem to do the job.  I willl put it before the person requesting
the information and see if it works for him.  The purpose of why
things are being used is always helpful and I should maybe have been
more helpful in that regard.  What we are doing is tracking time when
tools have to be calibrated and when parts have to be replaced in an
aviation setting.   As it sometimes takes 3 to 6 months to order a
part I want a count down and then can conditionally format it to turn
color on reorder date.

Now the only hiccup is these issues sometimes get done ahead of time
which then would start the cycle again before the prior cycle was
complete...  example part calibrated Jan 1, 2008 does not need to be
recalibrated for 2 years.   August 2008 the part is replaced and the 2
year cycle entered on Jan 1 is replaced by a new 2 year cycle starting
in Aug 2008....

My test shows that if you simply put in Aug date the result still
tells you the countdown from Jan.  Not that this information is not
needed but if you need to restart the cycle what would you have to
do??

.>My test shows that if you simply put in Aug date the result still

Put the date in where? C1?  A new date in C1 will give a new calculation.
If you don't want to retain the old count down, (it will become negative
after the due date), then try:

=IF(C1="","",IF(AND(E2="",DATE(YEAR(C1)+2,MONTH(C1),DAY(C1))-TODAY()<0),"Calibration
Overdue!",IF(E2<>"","Part
Replaced/Recolibrated",DATE(YEAR(C1)+2,MONTH(C1),DAY(C1))-TODAY())))

In E1 and copy down as far as required.

If there is no date in Column B in the Row below then the cell in Column E
will apprear blank.

If the Calibration period has expired and no Recalibration/Replacement has
been carried out then it will say "Calibration Overdue!"

If the part has been Recalibrated or Replaced and a new date inserted in
Column B of the Row below then it will say  "Part Replaced/Recalibrated"

All the rest of the time it will tell you how many days there are left until
the next due date.

On Mar 7, 3:03 pm, "Tyro" <T...@hotmail.com> wrote:

Thank you for the info on dates and how Excel views them.  There must
be a very simple way to format a cell to tell what the number of the
current day is and does it take into account leap years.  What if you
were wanting to count dates from pre 1900?

As to the formula given by Sandy and Ken I have tested it out and it
does seem to do the job.  I willl put it before the person requesting
the information and see if it works for him.  The purpose of why
things are being used is always helpful and I should maybe have been
more helpful in that regard.  What we are doing is tracking time when
tools have to be calibrated and when parts have to be replaced in an
aviation setting.   As it sometimes takes 3 to 6 months to order a
part I want a count down and then can conditionally format it to turn
color on reorder date.

Now the only hiccup is these issues sometimes get done ahead of time
which then would start the cycle again before the prior cycle was
complete...  example part calibrated Jan 1, 2008 does not need to be
recalibrated for 2 years.   August 2008 the part is replaced and the 2
year cycle entered on Jan 1 is replaced by a new 2 year cycle starting
in Aug 2008....

My test shows that if you simply put in Aug date the result still
tells you the countdown from Jan.  Not that this information is not
needed but if you need to restart the cycle what would you have to
do??

Hi Sandy,

I noticed that without VALUE, if I double click that cell then click
elsewhere the formatting automatically reverted to date. With VALUE,
after reformatting back to General it stayed that way after double
clicking in then clicking out.

BTW, my only reason for not using TODAY was that I didn't think of it,
I don't work with dates all that often.

Ken Johnson