Time

• This is chapter 12
• I will use This dataset
• Limits
  • 1/1/1900 is the "first" date
  • 12/31/9999 is the "last" date.
• Dates/times are a serial number.
  • Create a new worksheet called Sandbox
  • Enter =now() in cell a1.
  • Format this cell as a number.
    • Extend out to quite a few decimal places.
  • Copy the to cell B1, but paste as a constant
    • Subtract the two.
  • Enter 0 in cell A3
    • Format this as a date.
    • In the cell below, enter =1/24
    • Format this as a date
    • In the cell below enter =1/(24*60)
    • Format this as a date.
  • As an aside, go to custom format
    • Note that you can apply your own format strings.
  • In general, the integer part of a date number is the day information
  • The decimal part of the date number is the time.
• Apparently Excel supports two different date format types
  • The one we just explored is based on 1/1/1900 as the first date.
  • In the other 1/1/1904 is the first date.
  • In the advanced options, you can change the date system
  • 
  • The 1904 selection lets you have negative times.
  • But is no longer the standard.
  • Try B1-A1
    • Note how excel goes nuts with a negative number formatted as a date.
    • Also note how excel tries to format dates as dates no matter what.
• Entering Dates
  • mm/dd/yy woks
  • month day, year works well too
  • day month year seems to work.
  • He cautions us that the depends on settings.
  • But note, the edit bar for the date is different.
• Entering Times
  • Excel seems to be looking for a :
  • 11:00 works
  • 11:00 pm works too
  • 5 pm is good
• Try 1/1/2000 25:00
• Problems with dates
  • Apparently excel is based on Lotus 1 2 3
  • And they deliberately copied a date bug.
    • 2/29/1900 was not a leap year
    • But excel accepts it as a valid date.
    • This is not true for 2/29/2100
    • Remember divisible by 4, leap year, dividable by 100 not a leap year, dividable by 400 leap year.
  • Pre 1900 dates
    • There is really noting you can do about it.
  • Entering Dates
    • 2029 is a "boundary Year".
    • Enter 1/1/28
    • Enter 1/1/29
    • Enter 1/1/30
    • The authors suggest always using the full year to avoid problems.