Date & Time

Something you should have changed before 2000

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 settins is used by Excel for the Headings and Footings under page setup.

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.

System Date/Time into a Cell or within Code

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

Ctrl+;	date	(ctrl+semicolon)
Ctrl+:	time	(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()	date & time
=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

Formatting for Date and Time

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 time fractional day.
Serial for time is a fractional day.  One hour is 1/24 day, or approximately 0.4166667 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)

Time is recorded in 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.

Date Intervals, 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")

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 last solution using COUNTIF was posted by Tom Ogilvy 2000-11-21.

Date in advanced filter

"<"&TODAY()

Adding six months to a date

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. 

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

Time Sheets, providing for start and end time through midnight

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

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

The following formula was posted by Glenn Schwandt 1999-11-24 in worksheet.functions utilizing a rounddown 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

Fixup for Dates and Times entered as Text

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

	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 "a" 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

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.

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

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

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
7	322	=DATE(YEAR(A1)+A3+1,MONTH(A1), DAY(A1))-A2
Column B shows formula used in Column A,  =GetFormula(cell)

MS DB Q129277  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.

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

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

Q149689 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

Number of days in a Month

Refer to Q81694 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

Monday week starting dates

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

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

Using fill-handle with Rt-Mouse button to fill in a Calendar of weekdays

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 of fill-handle.

Ordinal numbers, and Ordinal numbers in dates

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

 =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 Speadsheet

Create a new worksheet with 1 weeks worth of dates

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

The following forumla 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

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

Rounding Time value to Quarter Hour

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

Calculating hours outside of Core range

>  =IF(B3>=A3,MAX(MIN(B3,G3)>A3,0)+MAX(B3>H3,0), MIN(B3+1,1+G3)>MAX(A3,H3))
>
>     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.

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 calcuate 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 (formated 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

• 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

• 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

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

Date & Time Functions in VBA

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)

ISDATE (a builtin VBA function)

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

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

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 similiar 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 maros 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. fR

Bus Schedule with PM times as boldface

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 Shedule page

Real-Time Clock in Excel

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

X

For time from the National Institute of Standards and Technology (NIST), see Time Information topic for more information.

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

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

See chrono.zip on Steve Bullen's site.
See threads on DejaNews Subject: Elapsed Time for Newsgroups: *public.excel* or specifically thread AN=399025887, specifically look for Stephen Bullen's reply.

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)

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
Sub beepme3()
  Beep
End Sub

Dates formatted in a VBA macro subroutine

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

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.

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 predeccessors, 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

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/nts.htm
See the Public Domain NIST Software area for downloadable synchronization software.  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 it with "ONCE" but did not like it, probably useful if you have an always online connection through a cable.  I prefer to see what the difference actually is. 

What Time Is It? (US Navy)
Another Realtime Clock (US Navy)
International Time zones

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 Tables or Calculators
   Daylight Savings Time, Dates for Change, in the US.

Clocks including in the System Tray and related downloads

• 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.
• See Ziff-Davis site http://www.zdnet.com/downloads/allhot/hotfree.html, look for TClockEx v1.4.2 -- to see seconds, date and more in tray clock, by Dale Nurden.
• TClockEx - Taskbar Clock Enhancer by Dale Nurden at his site. popup: System: 68% 1,136kB free, systray:Fri, 11 Feb 2000 - 20:53:04

Date Information, for those who are serious or just interested

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]

Related Items

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.

---

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.

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 at The Access Web -- Dev Ashish

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

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)

Calendars, <B662658A.1A7%scaliger@ensemble-fr.com> also see Q213795 below.

Microsoft Knowledge Data Base (MS KB)

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

• Q72578   XL: Converting Hours in Decimal Form to HH:MM:SS Format
• Q81026   XL: Calculating Days Remaining in a Time Period
• Q81694   XL: Formula to Calculate Number of Days in Month
• Q94870   XL: Changing the Date or Time Format in a Header or Footer
• Q99349   XL: Working with Time Periods That Exceed 24 Hours
• Q112361 XL: Negative Date/Time Value Displayed as Pound Signs (###)
• Q129277 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 Q129277 - datedif)
• Q151503 XL: How to Call a Macro Repeatedly on a Fixed Time Interval
• Q161607 XL97: Macro to Change the Date/Time Format in a Header/Footer
• Q164406 XL: How Microsoft Excel Works with Two-Digit Year Numbers.
• Q181370 XL: Microsoft Excel Incorrectly Assumes 1900 Is a Leap Year
• Q183626 XL: How the DateSerial Function Works with Year Arguments
• Q116281 XL: Visual Basic Procedures for Julian Date Calculations
• Q168435 XL: Maximum Times in Microsoft Excel, (10,000 in XL95 and later)
• Q213795 XL2000: How to Create a Monthly Calendar
• Q214391 XL2000: How Microsoft Excel Works with Two-Digit Year Numbers

---

You are one of many distinguished visitors who have visited my site here or in a previous location  since June 19, 1998.  Return to TOP,  Return From Whence You Came.

[Site Search -- Excel] 

Visit my Excel home page, or my Excel Site Index

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

---

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