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)

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)

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).

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)

	A	B	C	D
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)

	A	B	C
1	12/04/1952	Count of dates within December of any year
2	12/01/1999	6	=SUMPRODUCT(N(MONTH(A1:A20)=12))
3	11/16/2000
4	11/30/2000	Count of dates found within December, 2000
5	12/01/2000	4	=SUMPRODUCT((MONTH(A1:A20)=12)*(YEAR(A1:A20)=2000))
6	12/05/2000
7	12/15/2000	Count of dates found between Nov 16, 2000 and Dec 15, 2000
8	12/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
11	02/05/2002	Count of Tuesdays between two dates (i.e. Tue Feb 5, 2002 to Tue Feb 12, 2002)
12	02/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)

	A	B	C
1	08/15/2000	02/15/2001	=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
2	08/31/2000	03/03/2001	=DATE(YEAR(A2),MONTH(A2)+6,DAY(A2))
3
4	08/15/2000	02/11/2001	=A4 + 180
5	08/31/2000	02/27/2001	=A5 + 180
6
7	08/15/2000	02/13/2001	=A7 + 365/2
8	08/31/2000	03/01/2001	=A8 + 365/2
9
10	08/15/2000	02/28/2001	=DATE(YEAR(A10),Month(A10)+7,0)
11	08/31/2000	02/28/2001	=DATE(YEAR(A11),Month(A11)+7,0)
12
13	08/15/2000	02/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)))
14	08/31/2000	02/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.

	A	B	C	D	E	F
1	START	start.break	end-break	STOP	Worked
2	23:15	3:15	4:00	7:45	7:45	=(A2>B2)+B2-A2+(C2>D2)+D2-C2
3					7.75	=E2*24
4			format of		E1	h:mm
5			format of		E2	_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
6
7	23:15	0:00	0:00	7:45	8: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.

	A	B	C
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)

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

 
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

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

	A	B	C	D
1	Name	Birthdate	Friends	Formula
2	Bart	03/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.
3	Bobby	11/02/1985	2
4	Chris	02/02/1980	4
5	Leslie	12/25/1975	1
6	Pat	08/29/1986	6
7	Toby	02/14/1983	3
8
9	Feb	Invite	7	{=SUM((C2:C7)*(MONTH(B2:B7)=2))}
10	Feb	Birthdates	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.

	B	C	D
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.

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)

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

Monday week starting dates (#mondaystart)

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

A B 1  12/24/00   - 12/30/00   2 Sun 12/24/2000   3 Mon 12/25/2000   4 Tue 12/26/2000   5  Wed 12/27/2000   6 Thu 12/28/2000   7 Fri 12/29/2000   8 Sat 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.

By the way for own usage all of my dates on my computer and in Excel are in the ISO standard — The international standard date notation is YYYY-MM-DD
Note specifically 4 digit year and separated by hyphens, this does not get mixed up with any other date formats. But you must specify your date format.  Times are stored in Excel as numbers so I can read your spread sheets and you can read my spreadsheets in the format we each use.

Date & Time Worksheet Functions in Excel (#ws)

Refer to your HELP (F1)file for more information.

DATE	Returns the serial number of a particular date   syntax:    DATE(year, month, day
DATEVALUE	Converts a date in the form of text to a serial number   Example:  =DATEVALUE("8/22/55") equals 20323
DAY	Converts a serial number to a day of the month
DAYS360	Calculates the number of days between two dates based on a 360-day year
EDATE	Returns 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
EOMONTH	Returns the serial number of the last day of the month before or after a specified number of months
HOUR	Converts a serial number to an hour
MINUTE	Converts a serial number to a minute
MONTH	Converts a serial number to a month
NETWORKDAYS	Returns 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
SECOND	Converts a serial number to a second
TIME	Returns the serial number of a particular time
TIMEVALUE	Converts a time in the form of text to a serial number   Example:  =TIMEVALUE("2:24 AM") equals 0.1
TODAY	Returns the serial number of today’s date
WEEKDAY	Converts 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
WORKDAY	Returns 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
YEARFRAC	Returns the year fraction representing the number of whole days between start_date and end_date

Time Difference Calculations (#timediff)

Subtracting Time (#subtracttime)

	A	B	C	D	E	F	G
1	Format-->	General	General	[h]:mm	0.00
2	Employee	Start	End	Duration	Hours	=GetFormulaD(address)
3	Jan H.	8:20	10:55	2:35	2.58	D3: =C3-B3+(B3>C3)	E3: =24*D3
4	Pat T.	10:55	8:20	21:25	21.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)

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)

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)

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

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

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:

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

• Lotus 1-2-3 transition options interfere with entry of date and times, would suggest turning them off after all you are using Excel not 1-2-3. 
  Under Tools --> Options --> Transition turn off everything that says transition, and hit the [OK] button
• The DateWatch utility (or a similar 3rd-party program) can cause problems with entering fractions, it appears under tools menu as “Date Migration”, and can be turned off through Tools/Addins menu.  Rob Bovey 1999->01->15 <uVKu548X GA.205@cppssbbsa04>

Problems enter Fractions, but get Dates (#entryprob)

• Entering stock prices as 1/2 or 1/64 and getting dates: Solution enter 0 1/2 or 0 1/64 instead.  See Pasting fractional stock prices show up as dates in Excel for an attempt at fixing.

Countdown to a Date (#countdowny2k)

     =DATEVALUE("1-January-2001")-TODAY()&" days remaining in 2000"

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
DateSerial	DateSerial(year, month, day)
DateValue	DateValue(date) -- MyDate = DateValue("February 12, 1969")
Day	Day(date)
Hour	Hour(time) -- returns integer between 0 and 23
IsDate	IsDate(expression) -- returns True or False
Minute	Minute(time) -- returns 0 to 59
Month	Month(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")
Weekday	Weekday(date, [firstdayofweek])
Year	Year(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)

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

Converting Text Dates to Excel Dates (#MakeTrueDate)

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.

Adjusting Date Time Stamps from other systems (#adjust)

UNIX maintains date and time as seconds past Jan 1, 1970.  Convert to Excel use with
= DATE(1970, 1, 1) + (A1/24/60/60)
More information J.E. McGimpsey, 2001-09-26and function.

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. 

 

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   A B C D 3 8:00 Point B 7:00 Point F 4 12:00 Point C 12:00 Point E 5 13:00 Point D 16:00 Point D 6 17:00 Point E 17:00 Point C

New Bus Schedule Sheet   A B C D 3 8:00 Point B 7:00 Point F 4 12:00 Point C 12:00 Point E 5 1:00 Point D 4:00 Point D 6 5:00 Point E 5:00 Point C

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

Coverage Chart (#coverage)

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.    (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

• search on all words: "stop watch" real group:*excel*
  threadm=3A782332.5D8EC99E%40consumer.org
• search on all words: stopwatch record group:*excel*
  selm=eldUXJUlCHA.2000%40tkmsftngp04
  threadm=66sapo%2448e%241%40news-srv1.fmr.com

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/sunset,  Solar Position Calculator

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

• Alarm Clock, Turn your $3,000 computer into a $30 Alarm Clock.  Karen Kenworthy, writer for former Windows Magazine, writing their Power User’s column.  [LG]
• Clock.exe is supplied with Windows NT, and shows time hh:mm:ss and the date (but not day of the week).  Can be resized for a small display or even a full screen display.  Black text or outlined text on gray background.  Choice of local time or GMT, and can be displayed without title bar.
• TClockEx, Taskbar Clock Enhancer v1.4.2 «, Dale Nurden. http://www.rcis.co.za/dale/tclockex/index.htm,

  12:28:38 P

  16KB RAM avail 87% memory load, Fri 02/11/2000 (42/365) CPU xx%

  to see seconds, date and more in tray clock, CPU% not displaying on Win2000.  Mouse over shows your customized mini summary, mouse-click a calendar, right-click more options.  Display of time and/or date of your choice and colors on taskbar.  (originally seen in zdnet hotfree downloads)
• Title Bar Clock, v1.4 [62k] W9x/2k/XP FREE, {PC clock} TBC displays the day, month, time, and megabytes of free physical memory in the right side of the Title Bar of the main window
• Something frequently asked in newsgroups is how to place a clock into an Excel spreadsheet, something you should not do as it will severly affect/curtail your Excel usage.  (see #clock).

 

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)

• The Julian and Gregorian Calendars
• Quarters, how year is divided into quarters:  English (seasonal), Scots(different seasonal), US (tax).
• What time is it? VCR Clock Time | geekBlue.net, You'll find a link basically to the most annoying image in the world.
• Zulu and military time (posting) and GMT UTC, UT (wiki)

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)

• ###, date column needs to be widened, or cannot have a negative date or time.
• dateserial number instead of date, you could be in formula view; otherwise you need correct formatting.

Related Information (#related)

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.

• DATETIME one of Chip Pearson’s Excel Pages Chip’s site contains a series of how to’s and macros for dates and times.  Additional macros and how to’s can be found in holidays.  Another of Chip’s pages Date And Time Entry for XL97 describes how to enter time or dates without separators -- i.e.  1234 for time entry 12:34.  Third Friday of the Month and additional Worksheet Functions For Dates And Times similar to 4th Monday of a month (above).  Scheduling Procedures With OnTime 
• Date and Time [Excel] at The Access Web -- Dev Ashish
• Time and Date, A guide to time zones, calendars (by Country), sunrise/sunset, dates on/off daylight savings time.  World Clock and Configure your own cities for time checking.
• Since Excel will not accept dates before 1900, DATEDIF() will not work for dates before 1900. 
  As previously mentioned John Walkenbach has created as an addin to handle years 0100-9999 in his Extended Date Functions Add-In, which requires at least XL97.  http://www.j-walk.com/ss/excel/files/xdate.htm   and also on John’s site is Spreadsheet History. Also see XL: Excel Incorrectly Assumes 1900 Is a Leap Year -- The actual treatment of dates in John’s XDATE functions is that of VBA, which is better than the treatment found in Excel.
  Also of interest
• A downloadable employee time sheet can be found in John Walkenbach’s http://www.j-walk.com/ss/excel/files/index.htm
• Obtaining and Changing a File’s Created, Accessed and Modified Dates at Randy Birch’s “VBnet” (Visual Basic File API Routines) -- this page tells how to obtain/change a file's created, last access, and last modified dates.  Identifies five different times (System Time, Filetime, local systemtime or filetime, MS-DOS, Windows).  For instance Filetime is stored as 100-nanosecond intervals since Jan 1, 1601.  (does not specifically relate to Excel).  This example allows you to change the stored times.  (functions: GetFileDateString, GetSystemDateString)
• Local and GMT Times, Chip Pearson, conversions, the date and time stored as FILETIME is a Type comprised of two 32-bit Long integers, which together form a 64-bit integer (not supported in VB/VBA).  The value of the FILETIME is the number of 100-nanosecond intervals since 1-January-1601.  Site also has Conversions between UT and local Daylight Savings time.
• Calendars, <B662658A.1A7%scaliger@ensemble-fr.com> site is http://www.altcal.eu/, AltCal, an Excel spreadsheet to build julian or gregorian calendars from 4713 BC to ... 1465001 AD, also see Q213795 below.
• TimeCore Solo ~ time management for the Individual, 1.5.43 (last freeware version),(not Excel), {Time management} TimeCore Solo is a time tracking program with a recording engine that captures time worked on a task or project. [LockerGnome]
• U.S. National Debt Clock : Real Time
• World Time Clock

Related blog postings

Months of the World, by Rob van Gelder, dates can be formatted to any Country and explanation of formats that include code like [$-1409]dddd, d mmmm yyyy where the 1409 is LCID hexcode for New Zealand.

Related newsgroup postings

• Calendar control added for entry into active cell from a Calendar, Chip Pearson, 2002-02-24, misc.

Microsoft Knowledge Data Base (MS KB)

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

• KB72578   XL: Converting Hours in Decimal Form to HH:MM:SS Format
• KB81026   XL: Calculating Days Remaining in a Time Period
• KB81694   XL: Formula to Calculate Number of Days in Month
• KB94870   XL: Changing the Date or Time Format in a Header or Footer
• KB99349   XL: Working with Time Periods That Exceed 24 Hours
• KB112361 XL: Negative Date/Time Value Displayed as Pound Signs (###)
• KB129277 XL: Undocumented Worksheet Function DATEDIF
  (see note earlier about removal from MS KB or see Chip Pearson’s page for information pertaining to the missing KB129277 - datedif)
• KB151503 XL: How to Call a Macro Repeatedly on a Fixed Time Interval
• KB161607 XL97: Macro to Change the Date/Time Format in a Header/Footer
• KB164406 XL: How Microsoft Excel Works with Two-Digit Year Numbers.
• KB181370 XL: Microsoft Excel Incorrectly Assumes 1900 Is a Leap Year
• KB183626 XL: How the DateSerial Function Works with Year Arguments
• KB116281 XL: Visual Basic Procedures for Julian Date Calculations
• KB168435 XL: Maximum Times in Microsoft Excel, (10,000 in XL95 and later)
• KB213795 XL2000: How to Create a Monthly Calendar.  Also see 2002 Excel Calendar (in Template Gallery)
• KB214330 XL: Description of the differences between the 1900 date system and the 1904 date system in Excel, This article describes the two date systems and the problems that you may encounter when you use workbooks that use different date systems
• KB214094 XL: HOW TO: Use Dates and Times in Excel 2000
• KB214391 XL2000: How Microsoft Excel Works with Two-Digit Year Numbers
• KB287027 XL2002: Web Query Converts Numbers That Contain Hyphens into Dates.  solution: Excel 2002 provides a long needed option to Disable date recognition when importing data.
• KB812385 - XL2002: Cells Automatically Format with Hijri Date Format When You Type “a-” Before a Number.

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

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