Venturing into date formulas...

  • Thread starter Thread starter TechnoGram
  • Start date Start date
T

TechnoGram

Do not know if this is possible, but believe all is possible given the
right guidance...
Have a worksheet and shall we say in column C there is a date Jan 1,
2008
Now in column E I would like it to take the date from column C and
show the number of days left in a 2 year period from the original
input date of 1/1/08
So basically when you input 1/1/08 it would show say 730 in E and if
you change the date in C to February 1, 2008 it would show 699.
Is this possible?????
 
What defines the two year period? Is it two years from the year of the
date in C?

Maybe this is one way:

=(C1+730)-C1

HTH,
Paul
 
Dates are numbers to Excel. Jan 1, 1900 is day 1. Jan 1, 1950 is day 18264.
July 25, 1980 is day 29427. March 7, 2008 is day 39514.
When you format these numbers as dates you see something humans understand
as a date. To Excel, the dates are simply numbers.
And you can treat them as numbers. For example to add 37 days to a date,
simply =DateCell + 37 and format the answer as a date.

Tyro
 
I don't follow what you are saying:
So basically when you input 1/1/08 it would show say 730 in E and if
you change the date in C to February 1, 2008 it would show 699.
Click to expand...

That would only be 730 on 1/1/08 and February 1, 2008 would again be 730,
(ie two years for the date in Column C), not 669 unless you are getting the
end date form somewhere else.

Do you mean:

=DATE(YEAR(C1)+2,MONTH(C1),DAY(C1))-TODAY()

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Do not know if this is possible, but believe all is possible given the
right guidance...
Have a worksheet and shall we say in column C there is a date Jan 1,
2008
Now in column E I would like it to take the date from column C and
show the number of days left in a 2 year period from the original
input date of 1/1/08
So basically when you input 1/1/08 it would show say 730 in E and if
you change the date in C to February 1, 2008 it would show 699.
Is this possible?????
Click to expand...

Maybe...

=VALUE(DATE(YEAR(C1)+2,MONTH(C1),DAY(C1))-INT(NOW()))

Format E1 General

Ken Johnson
 
Ken,

If you are going toformat the cell as General why use VALUE()?



--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Ken,

If you are going toformat the cell as General why use VALUE()?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...

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
 
Dates are numbers to Excel. Jan 1, 1900 is day 1. Jan 1, 1950 is day 18264..
July 25, 1980 is day  29427. March 7, 2008 is day 39514.
When you format these numbers as dates you see something humans understand
as a date. To Excel, the dates are simply numbers.
And you can treat them as numbers. For example to add 37 days to a date,
simply =DateCell + 37 and format the answer as a date.

Tyro






- Show quoted text -
Click to expand...

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



Dates are numbers to Excel. Jan 1, 1900 is day 1. Jan 1, 1950 is day
18264.
July 25, 1980 is day 29427. March 7, 2008 is day 39514.
When you format these numbers as dates you see something humans understand
as a date. To Excel, the dates are simply numbers.
And you can treat them as numbers. For example to add 37 days to a date,
simply =DateCell + 37 and format the answer as a date.

Tyro






- Show quoted text -
Click to expand...

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
tells you the countdown from Jan.
Click to expand...

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.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Dates are numbers to Excel. Jan 1, 1900 is day 1. Jan 1, 1950 is day
18264.
July 25, 1980 is day 29427. March 7, 2008 is day 39514.
When you format these numbers as dates you see something humans understand
as a date. To Excel, the dates are simply numbers.
And you can treat them as numbers. For example to add 37 days to a date,
simply =DateCell + 37 and format the answer as a date.

Tyro






- Show quoted text -
Click to expand...

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