** Bill,

Make it 3:01 AM, just in case. :-)

Or, make it do DATE(new time + 3601), which will round to the most recent midnight your newly calculated time in the case where ten days later is at 11:00 PM and the DATE () function would round to the wrong midnight.

Doug


On Mar 2, 2010, at 11:02 AM, William Rentfrow wrote:

**

Fortunately this issue SHOULD be very straight forward.

Unfortunately - it isn't.

There's a button that calculates a person's period of eligibility to make changes to their HR benefits, etc.  You enter their employment anniversary date and hit the button and this performs a calculation:

$My Date$ - 864000 (i.e., minus 10 days).

Here's the interesting thing - when the date entered is  Daylight savings time - 3/15 this spring - the calculated value for the date time field returns 3/4/2010 11:00:00 PM.  Normally all of the times in this date/time field are left at 12:00:00 AM and are unused.

Technically speaking the calculation is EXACTLY correct.  3/4/2010 11:00:00 PM is exactly 10 days before 3/15/2010 12:00:00 AM - because 3/15 has an "extra" hour added that is a figment of our collective imagination.  Technically DST doesn't happen until 2:00 AM though but that's a matter for another time.

I was thinking about changing the times on these to default to 3:00:00 AM instead of 12:00:00 AM - but I'm open to suggestions.

William Rentfrow
Principal Consultant, StrataCom Inc.
wrentf...@stratacominc.com
Blog: www.williamrentfrow.com
O 715-592-5185
C 715-410-8056

_Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_



Doug

--
Doug Blair
+1 224-558-5462

200 North Arlington Heights Road
Arlington Heights, Illinois 60004



_Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_

Reply via email to