Date & Time

Location:   http://www.mvps.org/dmcritchie/excel/datetime.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

[y2k], [formatting], [intocell], [now], [datefmt], [vba], [addk], [days], [counting], [filter], [add], [thrumidnight], [military], [vbadates], [fixmmddyyyy], [totbymonth], [text2dates], [age (with DATEDIF)], [daysinmonth], [firstdate], [mondaystart], [julian], [filldates], [ordinal], [lastsaved], [sheetwithdates], [weeknumber], [ws], [timediff], [subtracttime], [rndqtrhr], [fees], [core], [date123], [entryprob], [countdowny2k], [vbadate], [isdate], [MakeTrueDate], [t2cols], [adjust], [debug], [bus], [coverage], [clock], [timers], [StopWatchcontd], [vbaformat], [gwash], [timeinfo], [clocks], [dateinfo], [interesting], [related]

[An example of a countdown timer for date was removed from this area that relied on Microsoft Virtual Machine,
Microsoft Virtual Machine is no longer supported, you should install JAVA to run JAVA applets.]

On This Day In History - MSN Encarta
Yahoo! - This Day in History

Something you should have changed before 2000 (#y2k)

There is enough confusion between US and UK dates without dealing with 2-digit years.

Suggest you make changes such as the following so that all of your dates will display as four digit years.  If you do not do this you may not be able to distinguish which part of the date is the year, the month, day; and have further difficulty knowing whether you actually have a 19xx year or a 20xx year.  Excel will keep track of dates but it may not be what you see displayed or you may not be sure.

Change your Regional Settings (shown with US values month/day/year)
      Short date: mm/dd/yyyy
            will show 12/28/1999
      Long date: mmmm dd, yyyy
            will show December 28, 1999
note this will change all of MS Office

In Excel under Format --> cells --> Custom -->> mm/dd/yyyy
      see HELP for more information.  The same letter combinations
      are used in Regional Settings as seen in Excel Help

The short date format in Regional settings is used by Excel for the General format.

The long date format in Regional settings is used by Excel for the Headings and Footings under page setup. 

Differences in Regional Settings may not be immediate apparent.  Time in the US like =TEXT(A1,"[hh]:mm") might be =TEXT(A1,"[TT}:mm") in another language, incorrect use results in a #VALUE! error.  Within double quotes such formulas may not translate in an exchange of workbooks (??).

When Excel has recognized through some means that you have a date -- entered as a date or assigned by a formula based on a cell that had a date, the short date form from your Windows Regional Settings will be used if your cell format is General.  You can override the default formatting by specifically formatting a the column, for instance, with the date format of your choice.

Date and Time Formatting   (#formatting)

Additional information on Formatting Numbers, Dates, and Times in general can be found in the HELP topic About number format codes for dates and times.  You will find that your Excel HELP is equivalent to the Excel XP pages referred to above, and you should be familiar with the HELP.  I have some examples for numeric formats on my formula= page.  For January: ; mmmm shows January, mmm shows Jan, mm shows 01, and m shows 1.  Similar for d, dd, ddd, dddd which would show as 3, 03, Thu, Thursday for Jan 3, 2002.

To format hours so that they don’t roll over into days format as [h]:mm or as [hh]:mm

An interesting format was pointed out by Dave Peterson where mm/dd/yyyy* dddd, places the date left justified (US format) and the day spelled out and right justified within the cell as ||11/14/2001      Wednesday||.  It is typical number formatting but interesting applied to dates.

System Date/Time into a Cell or within Code (#intocell)

Placing a current date, or time, or timestamp constant that will not change later

Ctrl+;           date  (ctrl+semicolon)
Ctrl+:   time  (ctrl+shift+colon)
Ctrl+;  (space) Ctrl+: date with time  (ctrl+semicolon)(space)(ctrl+Shift+colon)
ActiveCell.Value = Date  Equivalent usage in a macro for Date
ActiveCell.Value = Time  Equivalent usage in a macro for Time
ActiveCell.Value = Now  Returns current date and time in a macro
ActiveCell.Value = Timer  Returns seconds since midnight in a macro (timing usages)

Additional Date/Time related shortcuts

Ctrl+Shift+#     Apply the Date format with the day, month, and year
Ctrl+Shift+@Apply the Time format with the hour and minute, and indicate A.M. or P.M.

Placing a current date or time that will update on recalculation (#now)

The use of NOW() will place the system date and time into the cell  The format is dependent on your date and time settings (International settings).

TIME in VBA only goes down to hh:mm, to get hh:mm:ss you need to use NOW.  Excel does not provide a finer time accuracy.  There are some examples of using Time in Event macros upon change of another cell or by double-click on a cell.  If you need to time events to 10ms (hundreth of a second) using a timer started when you boot up see Simple Timer used for checking timing on code.

=NOW() date & time (only down to hh:mm:ss)
=Now() show date if formatted for date
=Now() show time if formatted for time
=int(Now())date only, must be formatted for date†
=mod(Now(),1)   time only, must be formatted for time†

  See "Number format codes for dates and times" in help.
    example of time format:  hh:mm:ss
    examples of date or custom date format:  mm/dd/yyyy  and  dddd mmmm dd, yyyy

Excel trickery:  if you enter the formula =NOW() into a cell and then hit F9 (calculate) without hitting Enter, it will convert the unentered formula to a constant without formatting of it's own, so you should format the column.

Formatting for Date and Time (#datefmt)

Formatting is done using Format --> cells --> date or custom

Help --> Find --> formats --> Custom number formats

--> For more information about number format codes for dates and times, click [>>]

Entering Dates into a Cell Formatted as General

Entering a month and day, or a month and year will cause the formatting to change for a General Cell.  This can also produce problems if the figure entered is actually a fraction and not a date.  (partial solution)

Entered as:
Displays
As
Generates
Format 
Actual
 value 
Actual
Date (US)
3/31 31-Mar d-mmm 36,616.00 03/31/2000
3/32 Mar-32 mmm-yy 11,749.00 03/01/1932
3/31/2000 03/31/2000 m/d/yy 36,616.00 03/31/2000
03/31/2000 03/31/2000 m/d/yy 36,616.00 03/31/2000
3/0 Mar-00 mmm-yy 36,586.00 03/01/2000
3 3 General 3.00 01/03/1900
    General -  

Date and Time serials

Serial for date only – has no decimal places.
Serial for date and time – has date serial plus the time component as a fraction of a day.
Serial for time – is a fractional day.  One hour is 1/24 day, or approximately 0.04166667 of a day as a decimal number.

Formula -- =GetFormula(cell) datestamp date serial
=NOW() 6/19/98 7:25 35965.31
=0.01 1/0/00 12:14 AM 0.01
=0.51 1/0/00 12:14 0.51
=10 1/10/00 0:00 10.00
=INT(NOW()) 6/19/98 0:00 35965
=MOD(NOW(),1) 1/0/00 7:25 0.309130787
=DATEVALUE(TEXT(NOW(), "mm/dd/yy")) 06/19/1998 35965.00
=DATE(NOW(), 1,1) 01/01/1998 35796.00
=DATE(year(now()),month(now())+1,0)
      (end of current month)
06/30/1998 35976.00
=DATE(year(now()),month(now())+1,1) 07/01/1998 35977.00
Date entered in Excel 95
See additional information below
Date formatted
mm/dd/yyyy
date serial
1/0/00 01/01/2000 36526
1/0/19 01/01/2019 43466
1/0/20 01/01/1920 7306
The timestamp shown is dependent on cell formatting.  Normally a date would appear without a zero time, and a time would appear without a zero date.

In XL95 the windowing for 2 digit dates (date window) is 00-19 represents 2000-2019, and 20-99 represents 1920-1999.  The windowing dates change with later versions of Excel.  XL97 and XL98(Mac) use 00-29 and 30-99.

Last Day of Current Month

Worksheet Function: -- end of Current Month
    =DATE(year(now()),month(now())+1,0)

Programming: end of current month: (NOW is a datetime serial and has both components)
    ActiveCell.Value = DateSerial(Year(Now), Month(Now) + 1, 0)
    ActiveCell.NumberFormat = "DDD MM/DD/YYYY"

You would actually format the entire column for best usage, without formatting in code.

To assign a variable
    Dim MyDate as Date
    ActiveCell.Value = DateSerial(Year(Now), Month(Now) + 1, 0)

VBA Examples for Date and TIME (#vba)*

Sub ss1()
 'As constants the following will not update
   [a1] = Int(Now)     'date
   [a2] = Now          'date and time
   [a3] = Date         'date
   [a4] = Date + Time  'same as now
 'As Worksheet Functions the following will update
   [a5] = "=Today()"   'current date into worksheet formula
   [a6] = "=now()"     'current date
   [a7] = "=now() - Today()"   'current time when recalculated
   [a7].NumberFormat = "hh:mm"
   [a8] = "=MOD(NOW(),1)"      'current time when recalculated
   [a8].NumberFormat = "hh:mm"
End Sub

Comparison of VBA and Worksheet Functions (#comparison)

While col A may have started out as format General, Excel and VBA will change the formatting from General.  VBA time and now in VBA will truncate seconds, so you see 06.98 secons from Excel NOW() and 07.00 from the Excel TIME, and 06.00 seconds from VBA.  Due to the truncation of seconds, VBA is not going to match your system clock.  VBA Excel now() will include fractional seconds.  If you want something for timing fractional seconds see Simple Timer on my “Slow Response” page used to time code to thousandths of a second.  I have yyyy-mm-dd as my date in Regional Settings and hh:mm for my time (not hh:mm A/P).
 Format as
 General
 Format as
 hh:mm:ss.00
 Formula or constant
 =personal.xls!GetFormula(An)
 VBA coding
 2005-04-20 09:10   09:10:06.00  38462.3820138889  [a2] = Now 'date and time
 2005-04-20  00:00:00.00   38462 [a3] = Int(Now) 'date
 2005-04-20   00:00:00.00  38462  [a4] = Date 'date
 2005-04-20 09:10  09:10:06.00   38462.3820138889 [a5] = Date + Time 'same as now
    
 Format as
 General
 Format as
 hh:mm:ss.00
 Formula or constant
 =personal.xls!GetFormula(An)
 VBA coding
 1900-01-00 09:10   09:10:06.98  =MOD(NOW(),1)  [a8] = "=MOD(NOW(),1)"
 09:10 AM  09:10:07.00   =TIME(HOUR(NOW()),MINUTE(
NOW()),SECOND(NOW()))
 [A9] = "=TIME(HOUR(NOW()),
MINUTE(NOW()),SECOND(NOW()))"
 2005-04-20 09:10   09:10:06.00  38462.3820138889  [a10] = Now 'date and time (timestamp)
 09:10:06 AM  09:10:06.00   0.382013888888889 [a11] = Time 'time

Adding an interval to a date (VBA code) (#addk)

Adding one month to the 31st in a calendar month can present a problem in interpretation.  Here is one solution:

To add one month to a date (i.e. 01/28/2001 through 01/31/2001 becomes 02/28/2001)
    x = DateAdd("m", 1, x - 1)
    'yyyy Year, q Quarter, m Month, y Day of year, d Day, w Weekday, ww Week, h Hour, n Minute, s Second

Time is recorded in Days (#days)

Dates and times are both recorded in units of days and the actual number may be referred to as a dateserial or a timeserial.  They can be entered in VBA as dateserial(year,month,day) and with timeserial(hour,minute,second).  As worksheet functions you would use =DATE(year,month,day) and =TIME(hours,minutes,seconds)   You can add the two to get a datetimeserial.

Time is recorded in days, so 1 hour = 1/24 day = .0417 day (approx); 1 minute = 1/(24*60) = .000694 days (approx); 1 second = 1/(24*60*60) = .00001157 days (approx)

Assuming that you actually have minutes and seconds multiply by 1440 and format as a number with 2 digits.

If on the other hand you really entered as hours and minutes multiply by 24 and format as a number with 2 digits.

  A B C
1 display 0:01:15 0.000868055555555556
2 display 1/0/00 12:00 AM =B1
3 Days 0.00087 =B1
4 days 0.000868056 =B1
5 hours 0.020833333 =B1*24
6 minutes 1.25 =B1*24*60
7 seconds 75.00 =B1*24*60*60
8      
9 display 1:15:00 0.0520833333333333
10 display 1/0/00 12:00 AM =B9
11 Days 0.05208 =B9
12 days 0.052083333 =B9
13 hours 1.25 =B9*24
14 minutes 75 =B9*24*60
15 seconds 4500.00 =B9*24*60*60

Example: Since 2:15 (2 hours 15 minutes = 0.093750 days) is stored as a fractional day you must multiply by 24 to get 2.25 hours.

Suppose you record units in B1 over time in B2 as in transmitting 8 million bytes in 8 minutes, you would put 8000000 in B1 and 0:8:00 in B2.  Example:

  A B C D =GetFormula(cell)
1 Bytes 8,000,000 400,000,000   8000000
2 minutes 0:08:00 0:08:00   0.00555555555555556
3 datetimeserial 0.005555556 0.005555556   =B2
4          
5 bytes/day 1,440,000,000 72,000,000,000   =B1/(B2)
6 bytes/hour 60,000,000 3,000,000,000   =B1/(B2*24)
7 bytes/min 1,000,000 50,000,000   =B1/(B2*24*60)
8 bytes/sec 16,666.67 833,333.33   =B1/(B2*24*60*60)
This is just an example and bears no relationship to any actual transmissions times.

The following shows how Excel interprets Data Entry:  — Yellow represents time greater than one hour — A3>=TIME(1,0,0)

 ABCD
 1  problems with mm:ss.ttt,  hh:mm,  days & time
 2 Default Display  General d hh:mm:ss.000 
 3 12:00.5   12:12:00 AM  0 00:12:00.500  entered as 12:00.5
 4 12:00.0   12:12:00 AM  0 00:12:00.000  entered as 12:00.0
 5  12:00   12:00:00 PM   0 12:00:00.000 entered as 12:00.
 6  12:00   12:00:00 PM   0 12:00:00.000 entered as 12:00
 7  12:00   12:00:00 PM   0 12:00:00.000 entered as 12:
 8  12   12:00:00 AM   12 00:00:00.000 entered as 12
 9  0.008333333  12:12:00 AM   0 00:12:00.000 entered as 0.008333333

Date Intervals, Counting (#counting)

 ABC
112/04/1952 Count of dates within December of any year
212/01/1999   6  =SUMPRODUCT(N(MONTH(A1:A20)=12))
311/16/2000 
411/30/2000 Count of dates found within December, 2000
512/01/2000   4   =SUMPRODUCT((MONTH(A1:A20)=12)*(YEAR(A1:A20)=2000))
612/05/2000  
712/15/2000 Count of dates found between Nov 16, 2000 and Dec 15, 2000
812/31/2000   5   =SUMPRODUCT(N(A1:A20>DATE(2000,11,15))*N(A1:A20<=DATE(2000,12,15)))
9    5   =countif(A1:A20,">=11/16/2000")-countif(A1:A20,">12/15/2000")
10    
1102/05/2002 Count of Tuesdays between two dates (i.e. Tue Feb 5, 2002 to Tue Feb 12, 2002)
1202/12/2002 2 =INT((A12-A11)/7)+IF(WEEKDAY(A11+4)+MOD(A12-A1,7)>=7,1)
The first two formulas are based on 2000-11-20 George Simms reply in misc and the third reworked from the first.  Cells A9:A20 are BLANK.  Note #VALUE! would result if any cell in range was otherwise not numeric.  For more information on SUMPRODUCT see Excel HELP.  The penultimate solution using COUNTIF was posted by Tom Ogilvy 2000-11-21.  The last solution was posted by George Simms 2000-02-19

Date in advanced filter (#filter)

"<"&TODAY()     -- see Sum Data for references to Advanced Filter.

Adding six months to a date (#add)

Adding six months to a date may have different interpretations.  For instance what is six months from a date if the month six months later does not have the same number of days.  (see Norman Harker link below)

 ABC
 108/15/200002/15/2001 =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
 208/31/200003/03/2001 =DATE(YEAR(A2),MONTH(A2)+6,DAY(A2))
 3   
 408/15/200002/11/2001 =A4 + 180
 508/31/200002/27/2001 =A5 + 180
 6   
 708/15/200002/13/2001 =A7 + 365/2
 808/31/200003/01/2001 =A8 + 365/2
 9   
1008/15/200002/28/2001 =DATE(YEAR(A10),Month(A10)+7,0)
1108/31/200002/28/2001 =DATE(YEAR(A11),Month(A11)+7,0)
12   
1308/15/200002/15/2001 =IF(DAY(DATE(YEAR(A13),MONTH(A13)+6,DAY(A13)))<>DAY(A13),DATE(YEAR(A13),MONTH(A13)+7,0),DATE(YEAR(A13),MONTH(A13)+6,DAY(A13)))
1408/31/200002/28/2001 =IF(DAY(DATE(YEAR(A14),MONTH(A14)+6,DAY(A14)))<>DAY(A14),DATE(YEAR(A14),MONTH(A14)+7,0),DATE(YEAR(A14),MONTH(A14)+6,DAY(A14)))

A shorter solution than seen in the last two rows adding 6 months is a solution attributed to Chip Pearson as described in a posting (as a tutorial) by Norman Harker (2002-10-13) in worksheet functions adding one month, you can add one month to a Jan 31st date for your own test.
  =DATE(YEAR(F2),MONTH(F2)+1,MIN(DAY(F2),DAY(DATE(YEAR(F2),MONTH(F2)+2,0))))

Also See use of DATEDIF on this page, and explanation of DATEDIF Worksheet Function on one of Chip Pearson's pages.  Not to be confused with dissimilar DATEDIFF VBA function.

Time Sheets, providing for start and end time through midnight (#thrumidnight)

Time is recorded as fractional days, so 24 hours = 1 day.

The following logical formula tests start time (A1) against end time (B1) and adds 1 if A1 is greater than B1.  If A1>B1 a true condition exists, value 1 (1=24 hours); otherwise, a false condition exists, value 0

     =(A1>B1)+B1-A1      'End time   -  start time
     =B1-A1+(B1<A1)      'a shorter formula, Tom Ogilvy, 2001-11-05, misc
     =MOD(B1-A1,1)       'a shorter formula, Ramu 2000-06-09 templates
A more complete example follows that includes break time.

 ABCDEF
1STARTstart.break end-breakSTOPWorked 
223:15 3:154:00 7:457:45 =(A2>B2)+B2-A2+(C2>D2)+D2-C2
3       7.75 =E2*24
4   format ofE1h:mm
5   format ofE2 _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
6       
723:15 0:000:00 7:458:30 =(A7>B7)+B7-A7+(C7>D7)+D7-C7

John Walkenbach has sample time sheets on his site see Related areas at end of this web page.

Entry of Military Time without separations (#military)

The following formula was posted by Glenn Schwandt 1999-11-24 in worksheet.functions utilizing a round down to eliminate right two digits, and a MOD to get the remainder of a divide by 100.
 ABC
1 TIME Time =getformula(Bx)
2 1 0:01:00 =ROUNDDOWN(A2,-2) / 2400 + MOD(A2,100) / 1440
3 2 0:02:00 =ROUNDDOWN(A3,-2) / 2400 + MOD(A3,100) / 1440
4 3 0:03:00 =ROUNDDOWN(A4,-2) / 2400 + MOD(A4,100) / 1440
5 1200 12:00:00 =ROUNDDOWN(A5,-2) / 2400 + MOD(A5,100) / 1440
6 1201 12:01:00 =ROUNDDOWN(A6,-2) / 2400 + MOD(A6,100) / 1440
7 2400 0:00:00 =ROUNDDOWN(A7,-2) / 2400 + MOD(A7,100) / 1440
8 3359 9:59:00 =ROUNDDOWN(A8,-2) / 2400 + MOD(A8,100) / 1440

Convert to or from Date Serial as Text Constants (#vbadates)

Note the use of Format(cell.Value, "Short Date") which will use your Regional Settings to format the short date.
Sub ConvertFromDateSerial()
    'Convert from  dateserial to formatted date text constant
    'For Excel dateserials on/after  March 1, 1900
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cell As Range
    On Error Resume Next    '-- in case no cells selected
    For Each cell In Intersect(Selection, _
            Selection.SpecialCells(xlCellTypeConstants))
       cell.Value = Format(cell.Value, "Short Date")
       'see help for "Named Date/Time Formats (Format Function)"
    Next cell
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Sub ConvertToDateSerial()
    'Convert from recognizable US date to date serial
    'For dates on/after  March 1, 1900 back to dateserial
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cell As Range
    On Error Resume Next    '-- in case no cells selected
    For Each cell In Intersect(Selection, _
            Selection.SpecialCells(xlCellTypeConstants))
        cell.Value = Int(DateValue(cell.Value))
        cell.NumberFormat = "general"
    Next cell
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Fixup for Dates and Times entered as Text (#fixmmddyyyy)

Assistance to Install a Macro or User Defined Function  on my Formula page.

Dates are in cells as mmddyyyy and time are hhmm, need to correct these text entries '01312000 to 01/31/2000 and '0136 to 01:36

=DATE(right(a1,4),left(a1,2),mid(a1,3,2) =TIME(left(b1,2),right(b1,2))
 
Sub Fixmmddyyyy()
Dim cell As Range
Selection.NumberFormat = "mm/dd/yyyy"
On Error Resume Next
For Each cell In Selection
  If Len(cell) = 8 Then
     cell.Value = DateSerial(Right _
      (cell.Value, 4), Left(cell.Value, _
        2), Mid(cell.Value, 3, 2))
  End If
Next cell
End Sub
 
Sub Fixhhmm()
Dim cell As Range
Selection.NumberFormat = "hh:mm"
On Error Resume Next
For Each cell In Selection
  If Len(cell) = 4 Then
     cell.Value = TimeSerial(Left(cell.Value, 2), _
       Right(cell.Value, 2), 0)
  End If
Next cell
End Sub


Sub fixhhmmss()
'posted as fixtime6(), fix mmss, hmmss, hhmmss
'DMcRitchie,  programming  2001-03-22
Dim x6 As String
Dim cell As Range
For Each cell In Selection.SpecialCells( _
       xlCellTypeConstants, xlNumbers)
  If cell >= 1 Then
    x6 = Right(Format(cell.Value, "000000"), 6)
    cell.Value = TimeSerial(Left(x6, 2), _
      Mid(x6, 3, 2), Right(x6, 2))
    'cell.NumberFormat = "hh:mm:ss"
  End If
Next cell
End Sub
Sub FixhhmmV()
Dim cell As Range
Dim vValue As Single
On Error Resume Next
For Each cell In Selection
  If InStr(1, cell.NumberFormat,":") = 0 Then
    If cell.Value > 1 Then    'Not a time serial yet
       cell.Value = TimeSerial(Int(cell.Value / 100), _
         Int(cell.Value - 100 * Int(cell.Value / 100)), 0)
    End If
  End If
Next cell
Selection.NumberFormat = "[hh]:mm"
End Sub
      To change entry at time of entry, Chip Pearson has Date And Time Entry for XL97
      and up to enter time or dates without separators -- i.e.  1234 for time entry 12:34. 

Using an Array Formula to total by Month (#totbymonth)

 ABCD
1NameBirthdate FriendsFormula
2Bart03/12/1984 3 The array formula is entered with
  Ctrl-Shift-Enter
do not enter the braces.

Read more about Array Formulas
on Chip Pearson's site.

3Bobby  11/02/1985 2
4Chris 02/02/1980 4
5Leslie12/25/1975 1
6Pat08/29/1986 6
7Toby 02/14/1983 3
8     
9FebInvite 7{=SUM((C2:C7)*(MONTH(B2:B7)=2))}
10FebBirthdates 2{=SUM(1*(MONTH(B2:B7)=2))}

A couple more Array formulas, find where “is next to 1 or is it “1”

    =SUMPRODUCT((E3:E24="a")*(F3:F24=1))      enter as array formulas
    =SUMPRODUCT((E3:E24="a")*(F3:F24="1"))    Ctrl+Shift+Enter

Converting Text Dates to Dates (#text2dates)

Either of these methods will create a date serial from the date not dissimilar from how your system is setup.  The formatting in the resulting cells will display the date format desired.  Now would be a good time to start using four digit dates if you aren’t already.
  1. Use Worksheet Function =Datevalue(text) will convert most anything if the date is in the same format as your system is set up, or
  2. Create the dateserial by placing a 1 in a cell on spreadsheet, copy it (Ctrl+C) and then select the column or whatever of text dates and use Paste Special feature with Multiply.  Better to copy an empty cell, the select the range to convert and use Edit, PasteSpecial, Add (see Make True Date).

Now what to do if the dates are not in the same format as your system.  UK text dates (i.e. dd/mm/yy) coming in on a US/Canada system (i.e. mm/dd/yy) to be formatted for US/Canada.  You may have to use MID and worksheet function =DATE(year,month,day).

Converting Dates to Text (Dates2Text)

To get a date into text so that it can be left justified and span columns.
=TEXT(NOW(),"mmmm dd, yyyy")

Age in Years, Months, Days using DATEDIF Worksheet Function (#age)(#datedif)

The following shows age in Years + Months + Days
  A B
1 03/27/1989 3/27/89
2 05/09/1998 =TODAY()   same as INT(NOW())
3 9 =DATEDIF(A1,A2,"y")   age in years
4 1 =DATEDIF(A1,A2,"YM")   plus months
5 12 =DATEDIF(A1,A2,"md")   plus days
6 Calculate number of days to next birthday (also see days to upcoming anniversary date)
7 322 =DATE(YEAR(A1)+A3+1,MONTH(A1), DAY(A1))-A2
Column B shows formula used in Column A,  =GetFormula(cell)

As long as you have date & time in each cell as entered by Excel you simply subtract the earlier timestamp from the later timestamp to get a differences in days and/or hours and/or minutes but since months do not have the same number of days and years don't have the same number of days, you probably want to use the DATEDIFF Worksheet Function to get differences between dates as people think of these differences. 

Obviously the accuracy in figuring this is hard to determine but comes close to how people often perceive differences.  The accuracy to seconds is not valid (the first timestamp) is simply a date, but shows how to format hours, minutes and seconds with the TEXT function for formatting as text. 

As you can see February (29 days in leap year) is the base month for the calculation for start date in February when the day of the month in Feb is greater than the day of the end month of January.  When the start date and end date are both in months with 31 days there is a jump of two days in the results at that transition point.

 BCD
 1 Start  End Perceived Difference using DATEDIF
 2 2004-02-28 15:43:00  2007-01-13 12:20:08  2 years, 10 months, 15 days, 20 hours, 37 minutes, and 08 seconds
 3 2004-02-29 15:43:00  2007-01-13 12:20:08  2 years, 10 months, 14 days, 20 hours, 37 minutes, and 08 seconds
 4 2004-03-01 15:43:00  2007-01-13 12:20:08  2 years, 10 months, 11 days, 20 hours, 37 minutes, and 08 seconds
 5 2005-03-02 15:43:00  2007-01-13 12:20:08  1 years, 10 months, 10 days, 20 hours, 37 minutes, and 08 seconds
 6 2006-01-12 23:00:00  2007-01-12 22:00:00  0 years, 11 months, 30 days, 23 hours, 00 minutes, and 00 seconds
 7 2007-01-12 23:00:00  2007-01-13 12:20:08  0 years, 0 months, 0 days, 13 hours, 20 minutes, and 08 seconds
 8 2007-01-13 00:00:00  2007-01-13 12:20:08  0 years, 0 months, 0 days, 12 hours, 20 minutes, and 08 seconds

B2:  38045.6548611111     (value in days of start))
C2:  =NOW()
D2:  =DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"y")&" years, "&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"ym")&" months, "&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"md")&" days, "&TEXT(MOD(C2-B2,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds""")

The  -(MOD(B2,1)>MOD(C2,1))  represents a correction downward of the ending date when the time of day of ending date is less than the time of day of the beginning date, so that the DATEDIF function can still be used.

MS DB KB129277  XL: Undocumented Worksheet Function DATEDIF (** See Next paragraph**)

Microsoft has removed DATEDIF from their knowledge database ( thread).  Chip Pearson maintains DATEDIF information on his site.  In XL95 you must use the Analysis Toolpak add-in.  The DATEDIF() worksheet function apparently comes with XL97 and is officially included in XL2000 and documented in the XL2000 help file (you can see here -- xlfctDATEDIF.htm).  Later versions of Excel dropped DATEDIF from the help file.

When thinking of DATEDIF there is a tendency to forget that
the difference between two dates in days is a simple subtraction.

KB216578 indicates that DATEDIF is not supported in the Office Worksheet Component meaning it is not available in interactive HTML created from Excel 2000.

Counting Years, Months and Days similar using VBA DATEDIFF to return a three cell array.  Tip 55 - John Walkenbach - also gives an idea of what an array formula is and how to implement.

KB149689 XL: Visual Basic Macro Examples for Working With Arrays.

[XL97][XL2000] If you have XL97 or later suggest dumping DATEDIF for John Walkenbach’s XDATEDIF Extended Date Functions Add-In, eliminating problems with negative dates involving subtraction in MS date system and incorrect leap years in older MS 1900 date system.  (also dates prior to 1900 below).

Using DATEDIF in a program

The following was posted by Chip Pearson on 6Dec1999 and probably can be found on his site.
Function Age(TheDate As Double) As String
Age = _
    CStr(Evaluate("=DATEDIF(" & TheDate & "," & CDbl(Now)  & ",""y"")")) & " years " & _
    CStr(Evaluate("=DATEDIF(" & TheDate & "," & CDbl(Now) & ",""ym"")")) & " months " & _
    CStr(Evaluate("=DATEDIF(" & TheDate & "," & CDbl(Now) & ",""md"")")) & " days"
End Function

Harlan Grove posted an interesting formula that does not require DATEDIFF to yield age:
  =YEAR(TODAY()-birthdate)-YEAR(0)

Number of days in a Month (#daysinmonth)

Refer to KB81694 XL: Formula to Calculate Number of Days in Month
Examples below for Jul 14, 1998 (07/14/1998)

  =DAY(DATE(YEAR(The_Date),MONTH(The_Date)+1,0))
 31  =DAY(DATE(YEAR("07/14/1998"),MONTH("07/14/1998")+1,0))
 
  =DAY(DATE(my_year,my_month+1,1)-1)
 31  =DAY(DATE(1998,7+1,1)-1)

First, Last, Nearest, Closest, nth weekday (4th Monday) in Month, Date calculations (#firstdate)

WEEKDAY Worksheet Function (1=Sun, 2=Mon, 3=Tues, 4=Wed, 5=Thur, 6=Fri, 7=Saturday)
First Monday of Month  (day of month 1-7):      1 + correction
Third Monday of Month (day of month 15-21):  15 + correction

(Dates are shown in US/Canada format mm/dd/yyyy)

 04/14/2001 7Sat36995
 First Day of Week 04/08/20011Sun =B1-WEEKDAY(B1)+1
 Last Day of Week 04/14/20017Sat =B1-WEEKDAY(B1)+7
 First Day of Month04/01/20011Sun =DATE(YEAR(B1),MONTH(B1),1)
 Last Day of Month04/30/20012Mon =DATE(YEAR(B1),MONTH(B1)+1,0)
 First Day of Year04/01/20011Sun =DATE(YEAR(B1),MONTH(B1),1)
 Last Day of Year12/31/20012Mon =DATE(YEAR(B1)+1,1,0)
 Closest Monday04/16/20012Mon =DATE(YEAR(B1),MONTH(B1),DAY(B1)+CHOOSE(WEEKDAY(B1),1,0,-1,-2,-3,3,2,1))
 Next Monday04/16/20012Mon =DATE(YEAR(B1),MONTH(B1),DAY(B1)+CHOOSE(WEEKDAY(B1),1,7,6,5,4,3,2))
 Next Monday04/16/20012Mon =A1-WEEKDAY(A1,2)+8
 1st Monday of Month04/02/20012Mon =DATE(YEAR(B1),MONTH(B1),CHOOSE(WEEKDAY(DATE(YEAR(B1),MONTH(B1),1)),2,1,7,6,5,4,3))
 2nd Monday of Month 04/09/20012Mon =DATE(YEAR(B1),MONTH(B1),7+CHOOSE(WEEKDAY(DATE(YEAR(B1),MONTH(B1),1)),2,1,7,6,5,4,3))
 3rd Monday of Month04/16/20012Mon =DATE(YEAR(B1),MONTH(B1),14+CHOOSE(WEEKDAY(DATE(YEAR(B1),MONTH(B1),1)),2,1,7,6,5,4,3))
 4th Monday of Month04/23/20012Mon =DATE(YEAR(B1),MONTH(B1),21+CHOOSE(WEEKDAY(DATE(YEAR(B1),MONTH(B1),1)),2,1,7,6,5,4,3))
 5th Monday of Month 04/30/20012Mon =IF(MONTH(DATE(YEAR(B1),MONTH(B1),28+ CHOOSE(WEEKDAY(DATE(YEAR(B1), MONTH(B1),1)),2,1,7,6,5,4,3)))=MONTH(B1), DATE(YEAR(B1),MONTH(B1), 28+CHOOSE( WEEDATE(YEAR(B1),MONTH(B1),1)),2,1,7,6,5,4,3)),"none")
3rd Wed. of Month 04/18/20014Wed =B1-DAY(B1)+22 -WEEKDAY(B1-DAY(B1)+4 )     -- Daniel M.
 Last Monday of Month
  see Date Calculations
04/30/20012Mon =DATE(YEAR($B$1),MONTH($B$1)+1,1)-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,6))
 Previous Monday04/09/012Mon =B1-CHOOSE(WEEKDAY(B1,1),6,7,1,2,3,4,5)
 For a different day of the week rotate the 2nd to last parameters in CHOOSE.
   i.e.  7,6,5,4,3,2,1 for Wednesday instead of 2,1,7,6,5,4,3 for Monday as used in some of the formulae.
 Formula in C1 & D1, downward  =IF(ISNUMBER(B1),WEEKDAY(B1),"")
 Formula in E1, downward   =personal.xls!getformula(E1)   see documentation for this User Defined Function (UDF) in Formula.htm
Weekday(date,1) week starts on Sunday (US default)— Numbers 1 (Sunday) through 7 (Saturday). Weekday(date,2) week starts on Monday — Numbers 1 (Monday) through 7 (Sunday).
Additional formulae and formulae with similar purpose can be found on my Date Calculations page.

Monday week starting dates (#mondaystart)

Week beginning on Monday, will assume Sunday belongs to the previous week of.
  Format column B as mm/dd/yyyy and column C as ddd.  Column C should only show “Mon”.
 
A2: =TODAY()
B2: =A2-MOD(A2-2,7)
C2: =B2

programming.  format column as mm/dd/yyyy

Function Mon_Start(sdate As Date) As Date
Mon_Start = sdate - (sdate - 2) Mod 7
End Function

Julian dates, in worksheet (#julian)

Works for numbers or text in the form yyddd, where yy is the year and ddd is the day within year. 
  i.e.  98003, 99003, 00003, 3, 01003, 1003.
=DATE(IF(INT(A1)>39000,INT(A1/1000)+2000,INT(A1/1000)),1,MOD(A1,1000))
These are, of course, the IBM computing “Julian dates”.  The real Julian dates are another matter they begin January 1, 4713 BCE (on the old Julian calendar).

Using fill-handle with Rt-Mouse button to fill in a Calendar of weekdays (#filldates)

You can specify how you want the filling to be done if use the RtMouse button instead of the LtMouse button when you use the fill handle to drag your selection down.  After dragging you will be asked what you want to do.
  || copy values, fill series, fill formats, fill values||
  || fill days, fill weekdays, fill months||
  || Linear trend, Growth Trend, series ... || with other choices where applicable.
  More information concerning fill-handle.

Ordinal numbers, and Ordinal numbers in dates (#ordinal)

Ordinal number, any of the numbers first, second, third, etc. (in distinction from one, two, three, etc. which are called cardinal numbers.  Also ordinal numeral.

21st and other dates with numbers as 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th, 11th, etc.

=A2&IF(INT(MOD(A2,100)/10)=1, "th", IF(MOD(A2,10)=1, "st", IF(MOD(A2,10)=2,"nd", IF(MOD(A2,10)=3, "rd","th"))))

21st May, 1999     and other dates with days of month as 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th, 11th, etc.

=DAY(A2)&IF(INT(MOD(DAY(A2),100)/10)=1, "th", IF(MOD(DAY(A2),10)=1, "st", IF(MOD(DAY(A2),10)=2,"nd", IF(MOD(DAY(A2),10)=3, "rd","th"))))& " " & TEXT(A2,"mmmm, yyyy")

or as a Function

Function OrdDate(arg)
    dd = Day(arg)
    mmmm = Format(arg, "mmmm")    '*Corrected*
    yyyy = Year(arg)
    Select Case Day(arg)
       Case 1, 21, 31
          OrdDate = dd & "st " & mmmm & ", " & yyyy
       Case 2, 22
          OrdDate = dd & "nd " & mmmm & ", " & yyyy
       Case 3, 23
          OrdDate = dd & "rd " & mmmm & ", " & yyyy
       Case 4 To 20, 24 To 30
          OrdDate = dd & "th " & mmmm & ", " & yyyy
    End Select
End Function
A macro by Ron Rosenfeld (see thread) changes the format rather than the text.  The macro is written for XL97 and has an advantage in not using a second cell to display a text format.  Written as a Worksheet_Change macro it will change the format for all dates on a particular worksheet when they are entered, changed or recalculated.  The big advantage is that the cell can continue to be treated as a numeric value.

Last Saved Date (#lastsaved)

 =FileDateTime(ActiveWorkbook.FullName)
Also see use in Pathname in headings, footers, and cells.  Will create a documentation page someday with all the documentation items and include the following Thomas Ogilvy reference: Read a Disk Directory to into a Spreadsheet, also see properties.

Create a new worksheet with 1 weeks worth of dates (#sheetwithdates)

Create a new worksheet one week of dates down Column A.  Dates created for the current week.  Example run on Fri 12/29/2000.  Constants for dates are generated not formulas with date functions.

Option Explicit
Sub Macro39()
    Sheets.Add
    Range("1:1,A:A").Font.Bold = True
    Columns("A:A").Select
    Selection.NumberFormat = "ddd mm/dd/yyyy"
    Range("a2").Formula = Int(Now()) _
        - Weekday(Int(Now())) + 1
    Range("A2").AutoFill _
        Destination:=Range("A2:A8"), _
        Type:=xlFillDefault
    Range("a1").Formula = _
        Format([a2], "mm/dd/yy") & Chr(10) _
        & " - " & Format([a8], "mm/dd/yy")
    Columns("A:A").EntireColumn.AutoFit
    Range("B2").Select
    ActiveSheet.Name = "D." & Format([a2], _
      "yyyymmdd") 'rename sheet
End Sub
   
 AB
1 12/24/00
  - 12/30/00
 
2Sun 12/24/2000  
3Mon 12/25/2000  
4Tue 12/26/2000  
5 Wed 12/27/2000  
6Thu 12/28/2000  
7Fri 12/29/2000  
8Sat 12/30/2000  

Week number -- European Style -- ISO Standard 8601 (#weeknumber)

The following formula was posted by Laurent Longre (1999-08-10), D is the date.
   =INT((D-SUM(MOD(DATE(YEAR(D-MOD(D-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)
In Europe weeks begin on a Monday and end on Sunday, even so the formula =WEEKNUM(date,2) which indicates that weeks begin on Monday will not work for European usage.  In the US the first week begins on January 1st, so the first week and the last week of a year may both have less than 7 days.

In the European style (ISO 8601) the week begins on a Monday and includes the first Thursday of the new year.  The reason for Thursday is that the majority of the days in the Mon-Sun week will be in the new year.  This is the same as saying the week with Jan 4th will always be the first week.

For additional information see “A Summary of the International Standard Date and Time Notation” at http://www.cl.cam.ac.uk/~mgk25/iso-time.html which contains information on ISO 8601 and references additional material.  Also see Pat McCotter's posted an article 1999->03->20.

Chip Pearson has followed this more closely you can find out more about week numbers, including English postal service on Chip’s site.

Date & Time Worksheet Functions in Excel (#ws)

Refer to your HELP (F1)file for more information.
DATEReturns the serial number of a particular date
  syntax:    DATE(year, month, day
DATEVALUEConverts a date in the form of text to a serial number
  Example:  =DATEVALUE("8/22/55") equals 20323
DAYConverts a serial number to a day of the month
DAYS360Calculates the number of days between two dates based on a 360-day year
EDATEReturns the serial number of the date that is the indicated number of months before or after the start date
  syntax:    EDATE(start_date, months)
  Example:  =EDATE(DATEVALUE("01/15/91"),1) equals 33284 or 02/15/91   Example:  =TEXT(EDATE(DATEVALUE("January"&" 15, 1999"),1),"mmmm") equals February
EOMONTHReturns the serial number of the last day of the month before or after a specified number of months
HOURConverts a serial number to an hour
MINUTEConverts a serial number to a minute
MONTHConverts a serial number to a month
NETWORKDAYSReturns the number of whole workdays between two dates.
    NETWORKDAYS(start_date,end_date,holidays)
    NETWORKDAYS("10/01/1998","12/01/1998","11/26/1998")
Result of above is 43 (61 days between).  Also see WORKDAY.
NOW Returns the serial number of the current date and time
SECONDConverts a serial number to a second
TIME Returns the serial number of a particular time
TIMEVALUEConverts a time in the form of text to a serial number
  Example:  =TIMEVALUE("2:24 AM") equals 0.1
TODAYReturns the serial number of today’s date
WEEKDAYConverts a serial number to a day of the week
  Example:  =WEEKDAY("2/14/907quot;) equals 4 (Wednesday), similarly
  Example:  =TEXT("4/16/90", "dddd") equals Monday
WORKDAYReturns the serial number of the date before or after a specified number of workdays.  This is the opposite of NETWORKDAY.
    WORKDAY(start_date,days,holidays)
YEAR Converts a serial number to a year
YEARFRACReturns the year fraction representing the number of whole days between start_date and end_date

Time Difference Calculations (#timediff)

Subtracting Time (#subtracttime)

 ABCDEFG
1Format--> General General [h]:mm 0.00   
2EmployeeStart EndDurationHours =GetFormulaD(address)
3Jan H. 8:2010:55 2:352.58 D3: =C3-B3+(B3>C3) E3: =24*D3
4Pat T. 10:558:20 21:2521.42 D4: =C4-B4+(B4>C4) E4: =24*D4

The formula to subtract times where the ending time might look like an earlier time would be: =C3-B3+(B3>C3)

The last part is a logical expression that adds 1 if start time is greater than end time, or 0 otherwise.  Time is measured in days so the addition of 1 is adding 24 hours to the equation.

You subtract one time from the other.  The result should be formatted as time but since you might end up with more than 24 hours you would format as [h]:mm instead of h:mm

Rounding Time value to Quarter Hour (#rndqtrhr)

Remember time is stored as days, so one quarter hour is 1/(24*4) days or .01041666 days.  The fraction will be more accurate.  MROUND is part of the Analysis Toolpak Add-in.
Rounding to nearest quarter hour:              =MROUND(A22,1/(24*4))
Rounding down to nearest quarter hour:    =FLOOR(A22,1/(24*4))
Rounding Up to nearest quarter hour:        =CEILING(A22,1/(24*4))
Rounding Up to nearest  half hour:             =CEILING(A22,1/(24*2))

Consulting Fees (#fees)

 CDEFGHIJ
8hh:mm Rounded
hh:mm
HRSRate/HR Billed @
$100/HR
 Addr Formula
94:06 4:30 4.5 100.00 $ 450.00  C9 0.170833333333333
107:29 7:30 7.5 100.00 $ 750.00  D9 = ROUNDUP(C9*48,0)/48
117:31 8:00 8.0 100.00 $ 800.00  E9 = ROUNDUP(C9*48,0)/2
128:00 8:00 8.0 100.00 $ 800.00  F9 100
1337:30 37:30 37.5 100.00 $ 3,750.00  G9 =F9 * ROUNDUP(C9*48,0)/2
Wages calculation with C9 having a time in hh:mm and you want to round up at 1/2 hour intervals shown in D9.  Column C is formatted as [hh]:mm

=Roundup(c9*48,0)/2                    format as 0.0 for decimal hours
=100* ROUNDUP(C9*48,0)/2         format as dollars not a time

Time is measured in days, 24 hours in 1 day. The roundup calculation does not handle fractions so multiply 24 by 2 get the roundup and then divide by 2

Overtime 1.0, 1.5. 2.0 x rate (#overtime)

Pay 1.0 * rate for hours up to 40
Pay 1.5 * rate for time within next 10 hours
Pay 2.0 * rate for additional hours over 50 hours

F30:  =24*SUM(F2,OFFSET(F30,-1,0)     Changes total excel times, SUM(F2:F29), to hours by multiplying by 24

G30:  = rate * (MIN(F30,40) + 1.5 * MAX(0,MIN(F30-40,10)) + 2 *MAX(0,MIN(F30-50)) )

Calculating hours outside of Core range (#core)

The basic concept is: number of hours before core hours + number of hours after core hours
  =IF(Shift_Start<Core_Start, Core_Start-Shift_Start,0) + IF(Shift_End>Core_End, Shift_End-Core_End,0)
but since time is not recorded with the dates this becomes more complicated.
>   -- corrections to formulas 2002-02-09 to fix errors when converted to HTML---
> c3: =B3-A3+(A3>B3)       is equivalent to  =IF(B3>A3,B3-A3,B3-A3+1)
> d3: =IF(B3>=A3,MAX(MIN(B3,G3)-A3,0)+MAX(B3-H3,0),  MIN(B3+1,1+G3)-MAX(A3,H3))
> e3: =IF(ABS(D3-F3)<0.0001,"yes","NO")
> f3: is the goal, it is what is to be expected from the calculations
>
>     G3  is the Core.start time
>     H3  is the Core stop time
>
> Don't know if this is a concern or not:  If  clock time goes through
> midnight and core start and stop times it will be incorrect, but your
> elapsed hours would exceed 16 hours as seen in my last tested values.
>
> David McRitchie   (1999/08/01)
>
> Tested example:
> --A-- --B-- --C-- --D-- -E- --F-- --G-- --H--
> Start Stop  Elap  calc. ^^^ GOAL c.start c.ent
> 23:00 07:00 08:00 08:00 yes 08:00 07:00 15:00
> 00:00 08:00 08:00 07:00 yes 07:00 07:00 15:00
> 03:00 11:00 08:00 04:00 yes 04:00 07:00 15:00
> 12:00 20:00 08:00 05:00 yes 05:00 07:00 15:00
> 16:00 00:00 08:00 08:00 yes 08:00 07:00 15:00
> 12:00 20:00 08:00 05:00 yes 05:00 07:00 15:00
> 23:00 15:00 16:00 08:00 yes 08:00 07:00 15:00
> 00:00 16:00 16:00 08:00 yes 08:00 07:00 15:00
> 03:00 19:00 16:00 08:00 yes 08:00 07:00 15:00
> 12:00 04:00 16:00 13:00 yes 13:00 07:00 15:00
> 16:00 08:00 16:00 15:00 yes 15:00 07:00 15:00
> 00:00 16:00 16:00 08:00 yes 08:00 07:00 15:00
> 04:00 20:00 16:00 08:00 yes 08:00 07:00 15:00
> 08:00 00:00 16:00 09:00 yes 09:00 07:00 15:00
> 12:00 04:00 16:00 13:00 yes 13:00 07:00 15:00
> 16:00 08:00 16:00 15:00 yes 15:00 07:00 15:00
> 20:00 12:00 16:00 11:00 yes 11:00 07:00 15:00
> 00:00 16:00 16:00 08:00 yes 08:00 07:00 15:00
> 04:00 04:01 00:01 00:01 yes 00:01 07:00 15:00
> 08:00 08:01 00:01 00:00 yes 00:00 07:00 15:00
> 04:00 04:00 00:00 00:00 yes 00:00 07:00 15:00
> 06:00 05:00 23:00 14:00 NO  15:00 07:00 15:00  == failed should be 15 hours not 14 hours.
>
> Similar information in a little different format, basically switch usage of G & H columns.
>
>       Rate --->    1           1.5                    Extra paid hours
> Start  End    Norm.  Prem. --  --  Extra Pay hours
> Time   Time   Hours  Hours --      --  From        To
> 17:00      01:00    03:00  05:00   --      --      20:00   6:00
> 01:00      09:00    08:00  05:00   --      --      20:00   6:00
> 09:00      17:00    08:00  00:00   --      --      20:00   6:00
> 17:00      01:00    08:00  05:00   --      --      20:00   6:00
> 01:00      09:00    08:00  05:00   --      --      20:00   6:00
> 09:00      17:00    08:00  00:00   --      --      20:00   6:00
>
> C3:        =B3-A3+(A3>B3)-D3
> D3:        =IF(B3>=A3,MAX(MIN(B3,H3)-A3,0)+MAX(B3-G3,0),  MIN(B3+1,1+H3)-MAX(A3,G3))
>
> To show time as a hours with decimal fraction multiply Excel hours by 24.

------
The following formula was posted 2002-02-09 by Daniel Maher and fixes a problem that I indicated in the first example.  This solution is for the second example.  Will at least be including this until I fix my own formula. DanielM 2002-02-09

=IF(CheckOut>=CheckIn,MAX(0,MIN(CheckOut,UpperBound)-
MAX(CheckIn,LowBound)),MAX(0,UpperBound-MAX(CheckIn,
LowBound))+MAX(0,MIN(CheckOut,UpperBound)-LowBound))

For the time OUTSIDE the boundaries
   of [LowBound ... UpperBound], it is
=(checkout<checkin)+CheckOut-CheckIn-Big_Formula_Above

If you want the HOURS, multiply by 24.

 
 ABCDE
 1 IN  OUT reported in core  out of core
 2 6:00   14:00  8:00  6:00  2:00 
 3 0:00   12:00  12:00  4:00  8:00 
 4 12:00   0:00  12:00  6:00  6:00 
 5 18:00   9:00  15:00  1:00  14:00 
 6 23:00   12:00  13:00  4:00  9:00 

C2: =(B2<A2)+B2-A2
D2: =IF(B2>=A2,MAX(0,MIN(B2,"18:00")-MAX(A2,"8:00")),MAX(0,"18:00"-MAX(A2,"8:00"))+MAX(0,MIN(B2,"18:00")-"8:00"))
E2: =(B2<A2)+B2-A2 - D2

grayed area for reference not used in calculation of columns D & E
(B2<A2)   is a logical express equates to either 1 (i.e. 24 hours), or 0 to be added/subtracted
------

Total core hours (9AM-5PM) between two datetimestamps, but only weekdays

On 8/8 8/9/1999 JM (jmklee) reply to Anthony R. Acosta.  (accepted as is)

I would like to calculate the difference in minutes between two dates, excluding the hours between 5pm to 8am and weekends.  For example if the

first date is 08/04/99 6:00pm and the end date is 08/05/99 9:00 am, I would like it to calculate as 60 minutes or one hour.

You can use the NETWORKDAYS function from the Analysis Tool.
A1 your first date and B1 your end date, both in full format mm/dd/yy hh:mm
You can have A1 and B1 separated with as many days you want.
The total time is in C1 (formatted with [h]:mm ) :

You might want to use notepad to eliminate end of line characters from these formulas:

 AB
1Sat 12/01/2001 07:00 Mon 12/03/2001 16:00
2=A6+A4=A8+A6
3  
48:00 Length of core hours for one day
57:00 start time (doesn’t count until either 8AM or 9AM) on 1st day
616:00 end time (one hour short on 9AM-5PM) on last day
712/01/2001 start date
812/03/2001 end date
9  
1007:00 Original Formula with NETWORKDAYS 9:00 - 17:00 original
1108:00 Original Formula with NETWORKDAYS but 8:00-16:00 instead of 9:00-17:00
1223:00 Formula without NETWORKDAYS corrected to use 9AM to 5PM
1324:00 Formula without NETWORKDAYS corrected to use 8AM to 4PM (spaces in formula removed)
1424:00 Formula without NETWORKDAYS corrected to use 8AM to 4PM (spaces in formula removed)
15 Formulas used in A9:A12 note 8 hour interval, with specific time range
1607:00 =MIN("8:00",MAX("0:00","17:00"+INT(A1)-A1))*NETWORKDAYS(A1,A1)+MIN("8:00",MAX(B1-"9:00"-INT(B1)))*NETWORKDAYS(B1,B1)+"8:00"*(MAX(0,NETWORKDAYS(A1,B1)-2)-(INT(INT(A1)/INT(B1))))
1708:00 =MIN("8:00",MAX("0:00","16:00"+INT(A1)-A1))*NETWORKDAYS(A1,A1)+MIN("8:00",MAX(B1-"8:00"-INT(B1)))*NETWORKDAYS(B1,B1)+"8:00"*(MAX(0,NETWORKDAYS(A1,B1)-2)-(INT(INT(A1)/INT(B1))))
1823:00 =MIN("8:00",MAX("0:00","17:00"+INT(A1)-A1)) +MIN("8:00",MAX(B1-"9:00"-INT(B1))) +"8:00"*(MAX(0, INT(B1)-INT(A1)+1)-2)-(INT(INT(A1)/INT(B1)))
1924:00 =MIN("8:00",MAX("0:00","16:00"+INT(A1)-A1))+MIN("8:00",MAX(B1-""8:00""-INT(B1)))+"8:00"*(MAX(0,INT(B1)-INT(A1)+1)-2)-(INT(INT(A1)/INT(B1)))
2024:00 =MIN(A4,MAX("0:00","16:00"+INT(A1)-A1))+MIN(A4,MAX(B1-"8:00"-INT(B1)))+A4*(MAX(0,INT(B1)-INT(A1)+1)-2)-(INT(INT(A1)/INT(B1)))

Problems entering Date or Time when Transition options in effect (#date123)

Problems enter Fractions, but get Dates (#entryprob)

Countdown to a Date (#countdowny2k)

Countdown to the New Millennium (year 2001).  Some additional Excel 2000 tips.
     =DATEVALUE("1-January-2001")-TODAY()&" days remaining in 2000"

VBA -- Visual Basic for Applications (Programming Language) -- VBA

Date & Time Functions in VBA (#vbadate)

Refer to your VBA HELP file for more information.  VBA Help is obtained where you can edit your code.  In XL97 and up it is Alt+F11, then F1, and in XL95 invoke F1 (Help) when editing a module sheet.

Date Returns a Variant (Date) containing the current system date.
DateDiff DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
Don’t confuse this with the DATEDIF Worksheet Function
DateSerialDateSerial(year, month, day)
DateValue DateValue(date) -- MyDate = DateValue("February 12, 1969")
DayDay(date)
HourHour(time) -- returns integer between 0 and 23
IsDateIsDate(expression) -- returns True or False
MinuteMinute(time) -- returns 0 to 59
MonthMonth(date)
Now Now -- Returns a Variant (Date) specifying the current date and time according your computer’s system date and time.
Second Second(time) -- returns whole number between 0 and 59, inclusive
TimeSerial TimeSerial(hour, minute, second)
TimeValue TimeValue(time) -- MyTime = TimeValue("4:35:17 PM")
WeekdayWeekday(date, [firstdayofweek])
YearYear(date)

If you want absolute control over adding a number of months to another date where the day of the month of the first date does not exist in the date that is xx months later. 

Function Addmonths(dateref As Date, _
       add_months As Long, Optional nextmonth as boolean) As Date
  Addmonths = Application.Min(DateSerial(Year(dateref), _
      Month(dateref) + add_months, Day(dateref)), _
      DateSerial(Year(dateref), Month(dateref) + add_months + 1, nextmonth))
End Function

ISDATE (a builtin VBA function)   (#isdate)

This example was tested on 08/05/2000 (Aug 5, 2000 US Format)

 ABCD
10      
11  08/01/2000   
12  09/01/200008/05/2000  
13  08/05/200010/01/2000  
14  08/05/200008/05/2000  
15  08/05/2000Hello  
16  08/05/200008/05/2000  
17     
 
Sub Ratman02()
Cells.Interior.ColorIndex = 0   'reset all interior color
Dim cell As Range
For Each cell In Application.Intersect _
     (Selection, ActiveSheet.UsedRange)
If IsDate(cell) Then
  If cell.Value > Date Then
    cell.Interior.ColorIndex = 3  'Red - FUTURE DATE
    Else
    cell.Interior.ColorIndex = 8  'Cyan -- valid date
  End If
Else: cell.Interior.ColorIndex = 14  'Teal -- NOT a Date
End If
Next cell
End Sub
More information on Colors in Excel and the Excel color palette.

Converting Text Dates to Excel Dates (#MakeTrueDate)

If the Text dates and your Regional Dates are both US date format, for a manual conversion:
  - Copy an empty cell
  - Select Range for cells to be converted
  - Edit, Paste Special, Add
  - Format range as Date
An alternative is to copy a cell with number “1“ and use Paste Special with Multiply, but it will cause empty cells to become zeros, so should not be used.

Use of a macro simply needs to reassign the value and Excel will recognize it as a date.  (only if your date formats are US)

Sub MakeTrueDate()
    'Converts Text Dates(US) to dates(US), Tom Ogilvy, 2001-03-24 programming
    Dim rng As Range
    Set rng = Intersect(ActiveCell.EntireColumn, _
    ActiveSheet.UsedRange) 'next assume first row is a header
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
    rng.NumberFormat = "mm/dd/yyyy"
    rng.Value = rng.Value
End Sub

Converting Text Entry Dates to Excel Dates with Text to Columns (#t2cols)

What to do when Text Entry Dates Do NOT Match your Regional Settings

Converting Text entry dates to Excel dates is easily done by multiplying by 1, but when the date entries do not match your regional settings you can use Text to Columns to fix up your dates.  Actually this would be more of a problem for those with UK/European/Australian dates than those with US dates.  My dates are US and my Regional short date is mm/dd/yyyy which having US dates is assumed to be m/d/y.  If you specify a month greater than twelve, Excel will assume you reversed the day and month -- there is such an example below.  This question was raised by someone in Australia working with MetaStock which generates an Excel spreadsheet with US text dates instead of UK/Australian dates.

 ABCDEF
1 Original Single column processed with
Text to Columns
Specified format for F3:G8
2Text to Columns (source) MDY DMY MDY DMY
3Format on row 4 @ m/d/yy m/d/yy ddmmmyyyy ddmmmyyyy
4Date4/5/99 04/05/199905/04/1999 05Apr199904May1999
5Date3/4/00 03/04/200004/03/2000 04Mar200003Apr2000
6Date9/10/99 09/10/199910/09/1999 10Sep199909Oct1999
7Date12/13/99 12/13/199912/13/1999 13Dec199913Dec1999
8Date13/12/01 13/12/0112/13/200113/12/01 13Dec2001
9Date24/9/01 24/9/0109/24/200124/9/01 24Sep2001
10shows alignment ooo oooooooooooooooooooooooooooooooooooooo oooooooooooooooooooooooooo

Adjusting Date Time Stamps from other systems (#adjust)

Debugging Date Entries (#debug)

Regional settings and cell formatting can cause problems with dates if anything is incorrectly applied.  This topic is to help you identify what you actually have.

One thing you can do to quickly determine if entries are Text or Numbers is to use:  Select ALL cells (Ctrl+A), Edit (Ctrl+G), [Special], GoTo, Number & Constants (only constants)

Dates are Numbers.  The format for a number can be changed at any time to another number format.  Data that was entered as text or with a text format must be reentered if the format is changed to a number format.

By formatting the cell range, then reentering the value you can convert text entries containing numbers to number constants.  The fastest way to reenter a single cell is to select cell, press F2, then Enter.

You can convert a lot of text entries containing numbers to number constants by multiplying by 1 or by adding a blank (truly empty) cell.  Select empty cell and copy (ctrl+c) then select the range, then edit, paste special, add.  You can effect a similar change by selecting a cell with a value of 1, copy (ctrl+c), select the range to be affected, paste special, multiply. 

You can use one of the REENTER macros on my Reformat page (join.htm) to speed up the process, there are several variations there, including TrimALL which specifically converts CHAR(160) which is the HTML symbol &nbsp; (non breaking space) to a space then trims spaces from the left and right sides of the cell value. 

 

Regional Dates in use are US:  short: mm/dd/yyyy   long: mmmm dd, yyyy
-- Some entries (E16:E25) below have formats overridden in cell.
    A  B  C  D  E  F  G 
 DATE ENTRY   Display  Formula    

 The Example to the left has entry as 6/7/99 and my
 Regional Settings are for the US, but my short date
 regional setting is modified to include the full 4 digit
 year so my Regional Setting Short Date format is
 mm/dd/yyyy -- interestingly the format shows as
 m/d/yy because that is the default US regional setting.

 Note cell B35 has code 0160 simulating an HTML
 symbol of &nbsp; (non breaking space).

 When trying to debug date entries note what you see
 1) in the formula bar
 2) in the cell
 3) in the format (Format, cell) 

06/07/1999   Entered as 6/7/99 with US Regional Settings    
06/07/1999   =LEN(A3)    
06/07/1999  36318  =TEXT(A4,"General")    
06/07/1999   07 Jun 1999  =TEXT(A5,"dd mmm yyyy")    
06/07/1999   Jun 07, 1999  =TEXT(A5,"mmm dd, yyyy")    
06/07/1999  TRUE   =ISNUMBER(A7)    
06/07/1999  D4   =CELL("format",A8)    
06/07/1999  v   =CELL("type",A9)    
10 06/07/1999  36318   =getformula(A10)    
11 06/07/1999   m/d/yy  =getformat(A11)    
12 06/07/1999  56  =CODE(RIGHT(A12,1))    
13                       
14  DATE ENTRY   Display  Formula      TEXT ENTRY  Display  Formula 
15  06/07/99   Entered as shown on Formula bar      6/7/99  Entered with quote on Formula bar 
16 07/06/1999   =LEN(A16)     6/7/99   =LEN(E16) 
17 07/06/1999  36318  =TEXT(A17,"General"     6/7/99   36318  =TEXT(E17,"General" 
18 07/06/1999   07 Jun 1999  =TEXT(A18,"dd mmm yyyy")      6/7/99  07 Jun 1999  =TEXT(E18,"dd mmm yyyy") 
19 07/06/1999   Jun 07, 1999  =TEXT(A18,"mmm dd, yyyy")      6/7/99  Jun 07, 1999  =TEXT(E18,"mmm dd, yyyy") 
20 07/06/1999  TRUE   =ISNUMBER(A20)     6/7/99  FALSE   =ISNUMBER(E20) 
21 07/06/1999  D1   =CELL("format",A21)     6/7/99  G   =CELL("format",E21) 
22 07/06/1999  v   =CELL("type",A22)     6/7/99  l   =CELL("type",E22) 
23 07/06/1999  36318   =getformula(A23)     6/7/99  6/7/99   =getformula(E23) 
24 07/06/1999   dd/mm/yyyy  =getformat(A24)     6/7/99   General  =getformat(E24) 
25 07/06/1999  56  =CODE(RIGHT(A25,1))      6/7/99 57  =CODE(RIGHT(E25,1)) 
26                       
27  has Char(160)
 so is text 
 Display  Formula      Column is
Text
 
 Display  Formula 
28  06/07/99    has Char(160)     6/7/99   Entered into Text field 
29  06/07/99  9   =LEN(A28)     6/7/99   =LEN(E28) 
30  06/07/99   06/07/99   =TEXT(A30,"General"     6/7/99 36318   =TEXT(E30,"General" 
31  06/07/99   06/07/99    =TEXT(A31,"dd mmm yyyy")     6/7/99   07 Jun 1999  =TEXT(E30,"dd mmm yyyy") 
32  06/07/99   06/07/99    =TEXT(A31,"mmm dd, yyyy")     6/7/99   Jun 07, 1999  =TEXT(E30,"mmm dd, yyyy") 
33  06/07/99   D1   =CELL("format",A33)     6/7/99  G   =CELL("format",E33) 
34  06/07/99   l   =CELL("type",A34)     6/7/99  l   =CELL("type",E34) 
35  06/07/99   FALSE   =ISNUMBER(A35)     6/7/99  FALSE   =ISNUMBER(E35) 
36  06/07/99   06/07/99    =getformula(A36)     6/7/99  6/7/99   =getformula(E36) 
37  06/07/99   dd/mm/yyyy   =getformat(A37)     6/7/99  @   =getformat(E35) 
38  06/07/99   160  =CODE(RIGHT(A38,1))      6/7/99 57  =CODE(RIGHT(E36,1)) 

Bus Schedule with PM times as boldface (#bus)

You cannot specify boldface in regular cell formatting.  Conditional Formatting could do the boldface but that is all.  A macro is needed to change the time and formatting so that that 1:00 and 13:00 both appear as 1:00 with the PM appearing in bold, and both without AM or PM.  Example:

Original Bus Schedule Sheet
 ABCD
38:00Point B 7:00Point F
412:00Point C 12:00Point E
513:00Point D 16:00Point D
617:00Point E 17:00Point C
   
New Bus Schedule Sheet
 ABCD
38:00Point B 7:00Point F
412:00 Point C12:00Point E
51:00 Point D4:00Point D
65:00 Point E5:00Point C

A more complete example with code can be seen on Bus Schedule page

Coverage Chart (#coverage)

 ABCDEFGHIJKLMNOPQRSTU
 7  NAME  HRS  8   9   10   11   12   13   14   15   16   17
 8  Arlene  7.5     X  X  X  X  X  X  X  X  X  X  X   X  X  X  X 
 9  Betty  7.0       X  X  X  X  X  X  X  X   X  X  X  X  X  X 
10  Cathy  7.5   X  X  X  X  X  X  X      X  X   X  X  X  X  X  X
11  Coverage   1   1   2   3   3   3   3   2   2   2   3   2   2   2   3   3   3   3   1 

B8:  =COUNTA($C8:$U8)*0.5
C11: =COUNTA(C$8:OFFSET(C11,-1,0))
Conditional formatting for the range C8:U10
CF1: =COUNTA(C8)
CF2: =NOT(COUNTA(C8))

1904 Date System vs 1900 Date System (#dates1904)

The difference in days between the two systems is 1462, which you may encounter if you try to change the date system in a workbook, or if you copied a worksheet from another workbook.  Tools, Options, Calculation, 1900/1904 Dates

Real-Time Clock in Excel (#clock)

Sometimes someone wants a real-time clock in their worksheet.  Personally I think the System time in the corner of the screen serves this purpose, and there are shortcuts for entering System Date/Time into a Cell or within code.  The solution here will produce screen blinking as the clock updates at one second intervals which may differ by up to one second from the system clock in your computer.
12:35:17 AM      For time from the National Institute of Standards and Technology (NITS),
see Time Information topic for more information.    [tycho clock missing] (US)
From:           Harald Staff

Here is code for a clock that runs in worksheet one, cell A1, unless You
type X in B1, then it stops.  Modify location and criteria&rsquos for Your own use.

Sub clock()
If ThisWorkbook.Worksheets(1).Range("B1").Value = "X" Then Exit Sub
ThisWorkbook.Worksheets(1).Range("A1").Value = Format(Now, "hh:mm:ss AM/PM")
Application.OnTime Now + TimeSerial(0, 0, 1), "clock"
End Sub

Best wishes Harald
(I got this code from this group some months ago, and I am sorry that I
did not archive the creator for later credits. Thank You, whoever You
are.)  See revised Real-Time Clock (below).

Revised Real-Time Clock

Similar to the above.  Harold supplied a revised version that recommends use of a start button and a stop button.  Instead of simply replacing the coding I have included both so you can see the coding.  I see the cursor move and blink momentarily every second on XL95.  You can create buttons using the Tools Form bar.  The buttons would not ordinarily coincide with cell boundaries, but they could appear to as in the following example.  Again repeating what was said before:  Personally I think the System time in the corner of the screen serves this purpose, and there are shortcuts for entering System Date/Time into a Cell or within code.  A clock on the worksheet will be stealing cycles whether you see the clock or not, even if the page isn’t active, and you will probably notice a slow down with large workbooks.  You can show seconds on the Task Bar Clock by using TClockEx without turning your expensive computer into a wall clock.
12:35:17 AM  Start Clock  Stop Clock   
Dim stopit As Boolean 'on top of module!

Sub startclock() 'assign start button
stopit = False
clock
End Sub

Sub clock()
If stopit = True Then Exit Sub
ActiveWorkbook.Worksheets(1).cells(1, 1).Value = _
Format(Now, "hh:mm:ss")
Application.OnTime (Now + TimeSerial(0, 0, 1)), "clock"
End Sub

Sub stopclock() 'assign stop button
stopit = True
End Sub
Note the Worksheets(1). This indicates the first worksheet in your workbook.  You might want to change that to a specific sheet such as Worksheets("Clock Sheet") or Worksheets("Sheet7") so as to not wipe out any sheet that just happens to have become the first sheet.  This also applies to the previous example.

Elapsed Time and Count-Down Timer (#timers)

See chrono.zip on Steve Bullen’s site.
See this thread in Google Usenet Archives, specifically look for Stephen Bullen’s reply.

Stop Watch, Alarm, and Count Down Timers

Excel does not have a SLEEP function so you have to calculate the time at which you will resume.  The following will issue three beeps, I give a little more time after the first beep. 

I still see timing done with a loop to waste machine cycles.  On a mainframe that would be well over 30 years behind the times.  Anyway I think the code I have below will work better across platforms and more to the point with processors with different speeds instead of a timing loop.

Sub Beeper()
  Beep
  start2 = Now() + TimeSerial(0, 0, 0.9)
  Application.Wait start2
  For i = 2 To 3
  start2 = Now() + TimeSerial(0, 0, 0.8)
  Application.Wait start2
  Beep
  Next i
End Sub
Stopwatch to time inner processing in seconds.  Timer is the number of seconds since midnight, so a correction has been included for negative number resulting from passing through midnight during execution.  (Limited to duration of 24 hours, longer times possible by including date) -- also see More on Stop Watches below.
Sub Timing_Test()
  Dim timing As Double 'timing will be shown at end
  timing = Timer     'Floating point register used
   ' .... lots of processing here ....
  timing = Timer - timing
  if timing < 0 then timing = timing + 1440 'midnight correction
  MsgBox Format(timing, "0.000") & " seconds"
End Sub
This example will sound an Alarm at a specified time.
Sub Alarm()
  Dim beepat As String
  beepat = InputBox("Give Alarm at", "hh:mm:ss " & _
    Format(Now, "mm:hh"), "17:00")
  If beepat = "" Then
    MsgBox "cancelled"
    Exit Sub
  End If
  Application.OnTime TimeValue(beepat), "BeepMe"
End Sub
Example of a Count Down Timer
Sub CountDownTimer()
  Dim beepat As String
  beepat = InputBox("Count down Timer hh:mm:ss  i.e. 10:00", _
    "Time now is " & Format(Now, "hh:mm:ss"), "3:00")
  If beepat = "" Then
    MsgBox "cancelled"
    Exit Sub
  End If
  Application.OnTime (Now + TimeValue(beepat)), "BeepMe"
End Sub
Sub beepme()
  Beep
  Application.OnTime (Now + TimeSerial(0, 0, 0.8)), "beepme2"
End Sub
Sub beepme2()
  Beep
  Application.OnTime (Now + TimeSerial(0, 0, 0.8)), "beepme3"
End Sub
 beepme3()
  Beep
End Sub
More on Stop Watches -- Continued (#StopWatchcontd),  Also take a look at my page on Slow Response

Inevitably someone is going to try to use Excel to time runners to 100ths of a second.  Without examining whether Excel is really feasible for this or not here are some newsgroup searches on how to do it.  The best solutions will probably require winmm.dll for high resolution.  All were found starting with: http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Dates formatted in a VBA macro subroutine (#vbaformat)

As text
[a10].Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
Range("A11").Value = Format(Now, "hh:mm:ss")
activecell.offset(0,4).value = Format(Now, "hh:mm:ss")

Assign datetime interval constant and format as date/time
activecell.offset(0,4).format = "mm/dd/yyyy hh:mm:ss AM/PM"
activecell.offset(0,4).value = Now

Dates Prior to 1900 and George Washington’s birthday (#gwash)

John Walkenbach has created as an addin to handle years 0100-9999 in his Extended Date Functions Add-In, which requires at least XL97.  0100-9999 is the date range supported by VBA, so don’t know what calendar changes, if any, are actually supported.

I don’t know which calendars are in use in John’s XDATE functions but anything has to be better than what is builtin to Excel.  I will mention the following excerpt from the 1990 “World Almanac” to highlight some difficulties of working with older dates. 

The British Government imposed the Gregorian calendar on all its possessions, including the American colonies, in 1752.  The British decreed that the day following Sept. 2, 1752, should be called Sep. 14, a loss of 11 days. All dates preceding were marked O.S., for Old Style.  In addition New Year’s day was moved to Jan. 1 from Mar. 25.  George Washington’s birth date, which was Feb. 11, 1731, O.S., became Feb. 22, 1732, N.S.

What is now the US had three different dates for the “Julian” to Gregorian calendar shift alone - 1582 for the areas under Spanish and French control, 1752 for areas under British control, and 1863 for Alaska, when it was purchased from Russia.

I think everyone would agree that that Excel is severely lacking in it’s ability to work with date calculations such as dates in 1800 especially since there are people living who were born in 1800’s and in treating 1900 as a leap year as had one of it’s predecessors, Lotus 1-2-3.


General Information on Date and Time (not necessarily related to Excel)

Time Information, for those who are serious or just interested (#timeinfo)

If you computer clock is consistently off by several minutes when you power up again, you probably have a bad battery (search: computer clock/CMOS batter*), and should check your OEM site for more information.  Replacing a battery yourself should be a cheap solution.

Official US Time
   NIST -- National Institute of Standards and Technology, and
   USNO -- US Naval Observatory

Time & Frequency Division, NIST (Boulder, Colorado)
 Time, Set Your Computer Clock Via the Internet,
   NIST Network Time Service (NTS) -- nistime-32bit.exe.lnk,
      http://www.boulder.nist.gov/timefreq/service/its.htm «
See the Public Domain NIST Software area for downloadable synchronization software.  Then if you want to set up with automatic synchronization, choose an NIST server near you then to have the time synchronize when you start up your machine add “once” within the Target box in the properties window of the shortcut
in the \Windows\Start Menu\Programs\Startup folder: 
i.e. C:\Internet\Nisttime\nistime-32bit.exe once  
I tried automatic synchronization originally, but did not like it, probably useful if you have an always online connection through a cable, but I prefer to see what the difference actually is, so I run the program myself each day.  Program uses sampling to improve accuracy.  If the time gets way out every day, you will probably have to replace a small battery in your computer. 

What Time Is It? (US Navy)
Another Realtime Clock (US Navy)
International Time zones
MS KB Q262680, List of Simple Network Time Protocol Time Servers on the Internet

Outside of the US
   http://www.arachnoid.com/abouttime/index.html excellent synchronization.
   GMT - Greenwich Mean Time, Universal World Time -- http://greenwich2000.com/
   Greenwich 2000 Home Page -- http://greenwich2000.com/
   World Time -- http://www.worldtime.com/cgi-bin/wt.cgi
   World Time Server   anywhere, anytime -- http://www.worldtimeserver.com/ -- get the Atomic Clock synchronization, perhaps questionable lots of adverts.

Some Date/Time Tables or Calculators
   Daylight Savings Time, Dates for Change, in the US.
    How to get local time based on the GMT or GPS time, GPS units typically report Universal Time (GMT time zone).
   Tools and Resources for Sun Calculations and Observations
   Wikipedia: astrological time solar lunar calculations, a reminder that Wikepedia does have articles on everything under the sun and beyond.

Calculators:  sunrise/sunsetSolar Position Calculator

Clocks including in the System Tray and related downloads (#clocks)

 

Date Information, for those who are serious or just interested (#dateinfo)

Information Please: Today in History, Events that happened on this date in history, world, US news, current events today calendar timeline.  [chronology, almanacs, Homework Center]

Mostly Useless but Interesting Information (#interesting)

Examples

  • Calendar Control in Excel, Using, Microsoft article.
  • Date Calculation Worksheet Examples.
  • Some additional date information can be found in used in footers.
  • =GetFormula(cell) can be used to show formula in use as seen in the examples on this page.
  • Working with dates, one frequently ends up with validly formatted dates appearing as text.  Reentering each of the dates will change them from text to dates.  F2 then Enter is faster, still better is to use a macro to accomplish this.  See ReEnter() macro.
  • Problems   (#problems)


    Related Information ON/OFF site

    Related Information on Other Sites

    My DATETIME page appears to have concentrated on formatting dates and times, while Chip Pearson appears to have concentrated more on calculations in his DATETIME pages.  Nothing remains static on web pages so you may want to recheck pages from time to time.  I also have a Date Calculation page which shows sample calculations in a simulated spreadsheet as a companion to this page.

    Related blog postings

    Related newsgroup postings

    Microsoft Knowledge Data Base (MS KB)

    Useful search argument in Excel For Windows area:    date and xl and time and xl

    This page was introduced on June 19, 1998. 

    [My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
    [Site Search -- Excel]     [Go Back]    [Return to TOP

    Please send your comments concerning this web page to: David McRitchie send email comments


    Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved