Date and Time Functions in Excel

Get work hours between dates and times in Excel

To calculate total work hours between two dates and times, you can use a formula based on the NETWORKDAYS function. Formula =(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN (MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start) *MOD(start,1),upper,lower) Explanation In the example shown, E5 contains this formula: =(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN (MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5) *MOD(B5,1),upper,lower) where “lower” is the named range H5 and “upper” is the named range H6. How this formula …

Get work hours between dates custom schedule in Excel

To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. Formula =SUMPRODUCT(MID(schedule,WEEKDAY (ROW(INDIRECT(start&”:”&end))),1)*ISNA (MATCH(ROW(INDIRECT(start&”:”&end)),holidays,0))) Explanation In the example shown, the formula in F8 is: =SUMPRODUCT(MID(D6,WEEKDAY(ROW (INDIRECT(B6&”:”&C6))),1)*ISNA(MATCH (ROW(INDIRECT(B6&”:”&C6)),holidays,0))) Which returns 36 hours, based on a custom …

How to get workdays between dates in Excel

To calculate the number of workdays between two dates, you can use the NETWORKDAYS function. NETWORKDAYS automatically excludes weekends, and it can optionally exclude a custom list of holidays as well. Formula =NETWORKDAYS(start_date,end_date,holidays) Note that NETWORKDAYS includes both the start and end dates in the calculation if they are workdays. Explanation For example, if you …

How to get year from date in Excel

If you need to extract the year from a date, you can use the YEAR function. Formula =YEAR(date) Explanation In the generic form of the formula above, the date must be in a form that Excel recognizes as a valid date. How the formula works The YEAR function takes just one argument, the date from …

Roll back weekday to Friday base on a particular date in Excel

Case study: If Monday, roll back to Friday To check the weekday of a date, and roll back to Friday when the date is a Monday, you can use the IF and WEEKDAY functions. Formula =IF(WEEKDAY(date)=2,date-3,date) Explanation In the example shown, the formula in C5 is =IF(WEEKDAY(B5)=2,B5-3,B5) How this formula works The WEEKDAY function returns …

How to join date and text together in Excel

To join a date with text, you can use concatenation with the TEXT function to control the date format.Generic formula =”text”&TEXT(date,format) Note: To control the date format, we use the TEXT function, which is designed to convert a number into text using a specified number format. Explanation In the example shown, the formula in E4 is: …

How to show last updated date stamp in Excel

To add a date stamp in a workbook to indicate a “date last updated”, you can use the TEXT function. Note: The TEXT function can apply number formatting to numbers just like Excel’s built-in cell formats for dates, currency, fractions, and so on. However, unlike Excel’s cell formatting, the TEXT function works inside a formula …

List holidays between two dates in Excel

To list holidays that occur between two dates, you can use a formula based on the TEXTJOIN and IF functions. Formula {=TEXTJOIN(“, “,TRUE,IF(dates>=start,IF(dates<=end,holidays,””),””))} Note: the TEXTJOIN function is new in Excel 2016. Explanation In the example shown, the formula in F8 is: {=TEXTJOIN(“, “,TRUE,IF(B4:B12>=F5,IF(B4:B12<=F6,C4:C12,””),””))} This is an array formula and must be entered with control + shift + enter. How …

How to calculate next anniversary date or birthday in Excel

To calculate the next anniversary date, you can use a formula based on the EDATE and DATEDIF functions. Case Study: Assume fixing anniversary date as June 1, 2017 in all examples. Formula =EDATE(date,(DATEDIF(date,as_of,”y”)+1)*12) Explanation In the example shown, the formula in D5 is: =EDATE(B5,(DATEDIF(B5,C5,”y”)+1)*12) This formula will work to calculate next upcoming birthday as well. How this …

How to calculate Next working/business day in Excel

To get the next working day, or next business day, you can use the WORKDAY function. Formula =WORKDAY(date,1,holidays) Explanation In the example shown, the formula in C4 is: =WORKDAY(B4,1,holidays) How this formula works The WORKDAY formula is fully automatic. Given a date and days, it will add days to the date, taking into account weekends …

Next biweekly payday from date in Excel

To get the next payday – assuming a biweekly schedule, with paydays on Friday – you can use a formula based on the CEILING function. Formula =CEILING(date+1,14)-1 Explanation In the example shown, the formula in C6 is: =CEILING(B6+1,14)-1 Note: this formula assumes Excel’s default 1900 date system. How this formula works This formula depends on …

How to calculate future date say 6 months ahead in Excel

— Next business day 6 months in future To get a date 6 months in the future, on the next work day, you can use a formula based on the WORKDAY function, with help from EDATE. Formula =WORKDAY(EDATE(date,6)-1,1,holidays) Explanation In the example shown, the formula in C6 is =WORKDAY(EDATE(B6,6)-1,1,B9:B11) How this formula works Working from …

Pad week numbers with zeros in Excel

To pad week numbers (or any number) with zeros using a formula, you can use the TEXT function. Formula =TEXT(WEEKNUM(date,type),”00″) Explanation In the example show, D5 contains this formula: =TEXT(WEEKNUM(B5,21),”00″) Which returns the string “07”. How this formula works The TEXT function can apply number formats of any kind, including currency, date, percentage, etc. By …

Sum race time splits in Excel

If you need to add up (sum) up race time splits that are some combination of hours, minutes, and seconds, you can simply use the SUM function. However, you must take care to enter times with the right syntax and use a suitable time format to display results, as explained below. Formula =SUM(range) Explanation The …

Two ways to sum time over 30 minutes in Excel

To sum the total amount of time over 30 minutes, given a set of times that represent duration, you can use the SUMPRODUCT and TIME functions. Alternatively, use SUMIFS and COUNTIFS functions. Formula =SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0))) Explanation In the example shown, the formula in G5 is: =SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0))) where “times” is the named range C5:C14. How this formula works This formula …

Calculate time difference in hours as decimal value in Excel

To get the duration between two times in as decimal hour (i.e. 3 hrs, 4.5 hrs, 8 hrs, etc.) you can use a formula based on the MOD function. Formula =MOD(end-start,1)*24 Explanation In the example shown, the formula in D5 is: =MOD(B2-A2,1)*24 Excel hours In Excel, one day is the number 1, so 1 hour …

Calculate total hours that fall between two times in Excel

Case study You have a start time and an end time, and you want to know how many of the hours in between these two times overlap another time period in the day. For example, you want to know how many hours fall between 8:00 AM and 5:00 PM, or 7:00 PM to 7:00 AM, etc. …

How to calculate workdays per month in Excel

To calculate workdays per month, use the EOMONTH function together with the NETWORKDAYS function. Formula =NETWORKDAYS(date,EOMONTH(date,0),holidays) Explanation In the example shown, the formula in C4 is: =NETWORKDAYS(B4,EOMONTH(B4,0),holidays) Where “holidays” is the named range E3:E13. How this formula works First, it’s important to understand that the values in the Month column (B) are actual dates, formatted with the …

How to calculate working days left in month in Excel

To calculate the number of workdays remaining in a month, you can use the NETWORKDAYS function. NETWORKDAYS automatically excludes weekends, and it can optionally exclude a custom list of holidays as well. Formula =NETWORKDAYS(date,EOMONTH(date,0),holidays) Explanation In the example shown, the formula in C5 is: =NETWORKDAYS(B5,EOMONTH(B5,0),E5:E14) Were B5 contains a current date, and the range E5:E14 contains …

How to determine year is a leap year in Excel

If you want to test whether the year of a certain date is a leap year, you can use a formula that uses the MONTH, YEAR, and DATE functions. Formula =MONTH(DATE(YEAR(date),2,29))=2 Explanation In the example shown, the formula in cell C5 is: =MONTH(DATE(YEAR(B5),2,29))=2 How the formula works The core of this formula is the DATE …

Series of dates by day

This tutorials shows how to generates Series of dates increment by day from a single start date in Excel. If you need to generate a dynamic series of dates with a formula that increase by one day from a single start date, you can do so a very simple formula that simply adds 1 to each …

Generate series of dates by weekends in Excel

This tutorials covers how to generates Series of dates increment by weekends from a single start date in Excel. If need to generate a dynamic series of dates with a formula that include only future weekend dates (i.e. Sat and Sun), you can do so with a formula that uses the IF and WEEKDAY functions. Formula …

Calculate series of dates by workdays in Excel

This tutorials covers how to generate series of dates increment by workdays from a single start date in Excel If need to generate a dynamic series of dates that are workdays only (i.e. Monday through Friday), you can do so with a simple formula that uses the the WORKDAY function Formula =WORKDAY(date,1) Explanation In the …

Convert date string to date time in Excel

This tutorial shows how to Convert date string to date time in Excel using example below. To convert a date string to a datetime (date with time) you can parse the text into separate components then build a proper datetime. When date information from other systems is pasted or imported to Excel, it may not …

Convert date to Julian format in Excel

This tutorial shows how to Convert date to Julian format in Excel using example below. If you need to convert a date to a Julian date format in Excel, you can do so by building a formula that uses the TEXT, YEAR, and DATE functions. Formula =YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),”000″) Explanation Background “Julian date format” refers to a …

Convert date to month and year in Excel

This tutorial shows how to Convert date to month and year in Excel using example below. To convert a normal Excel date into yyyymm format (e.g. 9/1/2017 > 201709), you can use the TEXT function. Formula =TEXT(date,”yyyymm”) Explanation In the example shown, the formula in C6 is: =TEXT(B6,”yyyymm”) How this formula works The TEXT function …

Convert date to text in Excel

This tutorial shows how to Convert date to text in Excel using example below. If you need to convert dates to text (i.e. date to string conversion) , you can use the TEXT function. The TEXT function can use patterns like “dd/mm/yyyy”, “yyyy-mm-dd”, etc. to convert a valid date to a text value. See table below for …

Convert decimal hours to Excel time

This tutorial shows how to Convert decimal hours to Excel time using example below. To convert hours in decimal format to a value Excel recognizes as time, divide by 24. Formula =hours/24 Explanation In the example shown the formula in C5 is: =B5/24 which returns 0.04167, the equivalent of 1 hours. Cell D6 shows the …

Convert decimal minutes to Excel time

This tutorial shows how to Convert decimal minutes to Excel time using example below. To convert minutes in decimal format to a proper Excel time, divide by 1440. Formula =minutes/1440 Explanation In the example shown, the formula in C6 is: =B6/1440 Because B6 contains 60 (representing 360 minutes) the result is 60/1440 = 0.04167, since …

Convert decimal seconds to Excel time

This tutorial shows how to Convert decimal seconds to Excel time using example below. To convert seconds in decimal format to a proper Excel time, divide by 86400. Formula =seconds/86400 Explanation In the example shown, the formula in C6 is: =B6/86400 To display the result as time, apply a time format. Column D shows the …

Convert Excel time to decimal hours in Excel

This tutorial shows how to Convert Excel time to decimal hours using example below. To convert a valid Excel time into decimal hours, simply multiply by 24. Formula =A1*24 Explanation In the example shown, the formula in C6 is: =B6*24 which returns a value of 1. How this formula works In the Excel time system, …

Convert Excel time to decimal minutes

This tutorial shows how to Convert Excel time to decimal minutes using example below. To convert a valid Excel time into decimal minutes, you can multiply by 1440. Formula =A1*1440 Explanation In the example shown, the formula in C6 is: =B6*1440 which returns a value of 30. How this formula works In the Excel time system, …

Convert Excel time to decimal seconds

This tutorial shows how to Convert Excel time to decimal seconds using example below. To convert a valid Excel time into decimal seconds, you can multiply by 86400. Formula =A1*86400 Explanation In the example shown, the formula in C6 is: =B6*86400 which returns a value of 60, since there are 60 seconds in 1 minute. How …

Convert Excel time to Unix time in Excel

This tutorial shows how to Convert Excel time to Unix time in Excel using example below. To convert a time in Excel’s format to a Unix time stamp, you can use a formula based on the DATE function. Formula =(A1-DATE(1970,1,1))*86400 > Explanation In the example shown, the formula in C5 is: =(B5-DATE(1970,1,1))*86400 How this formula …

Convert text date dd/mm/yy to mm/dd/yy in Excel

This tutorial shows how to Convert text date dd/mm/yy to mm/dd/yy in Excel using example below. To convert dates in text format dd/mm/yy to a true date in mm/dd/yy format, you can use uses a formula based on the DATE function. Formula =DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2)) Explanation In the example shown, the formula in C5 is: =DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5,2)) Which …

Custom weekday abbreviation in Excel

This tutorial shows how to create custom weekday abbreviation in Excel using example below. To create a custom weekday abbreviation, you can use a formula based on the CHOOSE and WEEKDAY functions. With this approach, you can generate a custom one-letter abbreviation, two-letter abbreviation, or any weekday that makes sense in your particular situation. Formula =CHOOSE(WEEKDAY(date),”S”,”M”,”T”,”W”,”T”,”F”,”S”) …

Create date range from two dates in Excel

This tutorial shows how to Create date range from two dates in Excel using example below. To display a date range in one cell based on dates in different cells, you can use a formula based on the TEXT function. Formula =TEXT(date1,”format”)&” – “&TEXT(date2,”format”) Explanation In the example shown, the formula in cell E5 is: …

Count times in a specific range in Excel

This tutorial shows how to Count times in a specific range in Excel using example below. To count times that occur within a certain range, you can use the COUNTIFs function. Formula =COUNTIFS(range,”>=”&start,range,”<“&end) Explanation In the example shown, the formula in E7 is: =COUNTIFS(B5:B11,”>=”&E5,B5:B11,”<“&E6) How this formula works The COUNTIFS function takes one or more …

Count holidays between two dates in Excel

This tutorial shows how to Count holidays between two dates in Excel using example below. To count holidays that occur between two dates, you can use the SUMPRODUCT function. Formula =SUMPRODUCT((holidays>=start)*(holidays<=end)) Explanation In the example shown, the formula in F8 is: =SUMPRODUCT((B4:B12>=F5)*(B4:B12<=F6)) How this formula works This formula uses two expressions in a single array …

Count day of week between dates in Excel

This tutorial shows how to Count day of week between dates in Excel using example below. To count the number of Mondays, Fridays, Sundays, etc. between two dates you can use an array formula that uses several functions: SUMPRODUCT, WEEKDAY, ROW, and INDIRECT. Formula =SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(date1&”:”&date2)))=dow)) Explanation In the example shown, the formula in cell E6 …

Lookup and Reference Examples

Data Analysis Examples

Data Validation Examples