OzGrid

Dates and time Excel formulas reference sheet

< Back to Search results

 Category: [Excel]  Demo Available 

Dates and time Excel formulas reference sheet

 

=EDATE

Add a specified number of months to a date in Excel

=EDATE(start date, number of months)

=EOMONTH

Convert a date to the last day of the month (e.g., 8/19/2019 to 8/31/2019)

=EOMONTH(A2,1)

Date of the last day of the month, one month after the date in A2

=EOMONTH(A2,-3)

Date of the last day of the month, three months before the date in A2.

 

=DATE

Returns a number that represents the date (yyyy/mm/dd) in Excel

=DATE(2016, 6, 20) - returns a serial number corresponding to 20-June-2016

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - returns the first day of the current year and month

=DATE(2016, 6, 20)-5 - subtracts 6 days from May 20, 2016

=TODAY

Insert and display today’s date in a cell

=TODAY() - returns todays date

=TODAY()+7 – adds 7 days to the current date

=WORKDAY(TODAY(), 30) – adds 30 weekdays to todays date excluding weekends

 

=DATEVALUE(date_text) 

Converts a date in the text format to a serial number that Microsoft Excel recognizes as a date

=DATEVALUE("20-May-2015")

Returns 42144

=TEXT(value, format_text)

TEXT function in Excel to convert date to text

 

=TEXT(value, format_text)

For example, you can use the following formula to

=TEXT(B1,"mm/dd/yyyy")

Converts a date in cell B1 to a text string in the traditional US date format (month/day/year)

For example June, 8, 2016 to 06/08/2016

Note, this will be aligned left because it is text format and not date format

=DAY

=DAY(TODAY())

Provides the day of today's date

=WEEKDAY

=WEEKDAY(TODAY()) 

Provides a number corresponding to today's day of the week

=DATEIF

DATEDIF(start_date, end_date, unit) calculates the difference between 0 dates in days, months or years.

=DATEDIF(b2, TODAY(), "d") calculates the number of days between the date in b2 and today's date.

=DATEDIF(B2, B5, "m") providesthe number of complete months between the dates in B2 and B5.

=DATEDIF(B2, B5, "y") provides the number of complete years between the dates in B2 and B5.

 

= EDATE

=EDATE(B2, 5)  adds 5 months to the date in cell B2.

=EDATE(TODAY(), -10)  subtracts 10 months from today's date.

 

=WORKDAYS

WORKDAY(start_date, days, [holidays]) function provides a date ‘N’ workdays before or after the start date. It automatically excludes weekend days from calculations.

=WORKDAY(A1, 65, B2:B85)

adds 65 weekdays to the start date in cell A1, ignoring holidays in cells B2:B8

=NETWORKDAYS

Provides the number of whole workdays between two specified dates.

NETWORKDAYS(start_date, end_date, [holidays]) function provides the number of weekdays between two dates that you specify. It automatically excludes weekend days.

=NETWORKDAYS(C2, D2, E2:E5)

calculates the number of whole workdays between the start date in C2 and end date in D2, ignoring Saturdays and Sundays and excluding holidays in cells E2:E5

 

=MONTH

=MONTH(A2) provides the month of a date in cell A2.

=YEAR

Extracts and displays the year from a date (e.g., 7/18/2018 to 2018) in Excel

=YEAR(TODAY()) 

Provides the current year

Say A2 has 20 June 2016 then

=YEAR(A2) - Provides the year of a date in cell A2.

=YEARFRAC

Expresses the fraction of a year between two dates

=YEARFRAC(startdate,enddate) (e.g., 1/1/2018 – 6/6/2018 = 0.43)

=YEARFRAC(A1,B1)

How to convert time to seconds

If the time in Cell B2 is 2:40:35 AM and the total number of seconds is required then:

1 Make sure the time is formatted properly – go to Format Cells (press Ctrl + 1) and make sure the number is set to Time.

2 Use the =HOUR(B2) formula to get the number of hours from the time and multiply by 3600 (there are 3600 seconds in every hour)

3 Use the =MINUTE(B2) formula to get the number of minutes from the time and multiply by 60 (60 seconds in every minute)

4 Use the =SECOND(B2) formula to get the number of seconds from the time

5 Add up the three pieces to get the total seconds

6 Make sure it’s formatted properly as a Decimal, and not as Time (press Ctrl +1 and select Decimal)

 

=NOW()

=NOW() provides the current date and time as of today

Date Codes:

Month

·         m - month number without a leading zero.

·         mm - month number with a leading zero.

·         mmm - short form of the month name.

·         mmmm - long form of the month name.

Date Codes:

Days

d - days number without a leading zero.

dd - day number with a leading zero.

ddd - abbreviated day of the week.

dddd - full name of the day of the week.

Date Codes:

Years

yy - two-digit year.

Yyyy - four-digit year.

 

Convert US date format of mm/dd/yyyy to UK (rest of the world) format of dd/mm/yyyy

=TEXT(A1,"mm/dd/yyyy")

 

Date Codes:

Hours

·         h - hours without a leading zero, as 0-23.

·         hh - hours with a leading zero, as 00-23.

 

Date Codes:

Minutes

·         m - minutes without a leading zero, as 0-59

·         mm - minutes with a leading zero, as 00-59

Date Codes:

Seconds

·         s - seconds without a leading zero.

·         ss - seconds with a leading zero.

 

Date Codes:

Periods of the day

·         AM/PM - displays as AM or PM.

·         If not specified, 24-hour time format is used.

 

See also:

Free Training Course: Lesson 1 - Excel Fundamentals

Conditional Functions Reference Sheet
Excel different types of data reference sheet
Useful formulae reference sheet
Financial formulae reference sheet
Index and match reference sheet
Lookup formulae reference sheet
Maths functions excel formulae sheet

 

See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 


Gallery



stars (0 Reviews)