Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Excel Frequency Function Example
  • How to create running total in an Excel Table
  • How to sum a total in multiple Excel tables
  • Conditional Formatting New Rule with Formulas in Excel

References

  • How to use Excel LOOKUP Function
  • How to use Excel ROWS Function
  • How to get first column number in range in Excel
  • Left Lookup in Excel
  • How to use Excel VLOOKUP Function

Data Validations

  • Excel Data validation unique values only
  • Excel Data validation must begin with
  • How To Create Drop-down List in Excel
  • Excel Data validation date in next 30 days
  • Excel Data validation with conditional list

Category: Date and Time Functions

Excel Date and Time Functions return a dynamic date or time in a cell.

Convert Excel time to Unix time in Excel

by

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 …

Continue Reading

How to calculate nth day of year in Excel

by

To get the nth day of year based on a given date, you can use a formula based on the DATE and YEAR functions. Formula =date-DATE(YEAR(date),1,0) Explanation In the example shown, the formula in C5 is: =B4-DATE(YEAR(B4),1,0) With the date “June 1, 2016” in cell B4, the formula returns 153, since June 1st is the 153rd day …

Continue Reading

How to show last updated date stamp in Excel

by

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 …

Continue Reading

Calculate series of dates by workdays in Excel

by

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 …

Continue Reading

Calculate days remaining in Excel

by

If you need to calculate the days remaining from one date to another, you can use a simple formula that subtracts the earlier date from the later date. See example below; Formula =end_date-start_date Explanation In the example shown, the formula in D5 is: =C5-B5 How this formula works Dates in Excel are just serial numbers …

Continue Reading

Display the current date and time in Excel

by

This tutorial show how to Display the current date and time in Excel using the example below. If you need to display the current date and time in a worksheet, you can use the NOW function. The date and time will update whenever the worksheet is recalculated or opened. Formula =NOW() Explanation of how this formula …

Continue Reading

Get day name from date in Excel

by

If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date or to convert the date into a day name, there are several options depending on your needs. Formula TEXT(B4,”dddd”) Explanation Do you just want to display the day name? If you only want to display a day name, you don’t need a …

Continue Reading

Calculate expiration date in Excel

by

If you need to calculate an expiration in the future, you can use a variety of formulas. See example below; Formula =A1+30 // 30 days Explanation In the example shown, the formulas used in column D are: =B5+30 // 30 days =B5+90 // 90 days =EOMONTH(B7,0) // end of month =EDATE(B8,1) // next month =EOMONTH(B7,0)+1 …

Continue Reading

Display the current date in Excel

by

This tutorial show how to Display the current date in Excel using the example below. If you need to display the current date in a worksheet, you can use the TODAY function. This date will update whenever the worksheet is recalculated or opened.  Formula =TODAY() Explanation of how this formula works Simply enter the formula above …

Continue Reading

Check If Two Dates are same month in Excel

by

This tutorial shows how to compare dates in Excel. To test two dates to see they both have the same month, you can do so with a simple formula that uses the MONTH function. Formula =MONTH(date1)=MONTH(date2) Explanation In the example shown, the formula in cell D6 is: =MONTH(B6)=MONTH(C6) How the formula works In this case, …

Continue Reading

Calculate number of hours between two times in Excel

by

This tutorials shows how to calculate number of hours between two times in Excel. To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary …

Continue Reading

Display Days until expiration date in Excel

by

This tutorial show how to Display Days until expiration date in Excel using the example below. To calculate the days until an expiration date, you can use a simple formula based on the TODAY function. Formula =A1-TODAY() Explanation of how this formula works In the example shown, the formula in C6 is: =B6-TODAY() Dates in Excel …

Continue Reading

Get month name from date in Excel

by

If you need to get the month name (i.e. January, February, March, etc.) from a date, you have several options depending on your needs. Formula =TEXT(date,”mmmm”) Explanation To convert the date into a month name If you want to convert the date value to a text value, you can use the TEXT function with a custom number …

Continue Reading

Basic Overtime Calculation Formula in Excel

by

If you need to find basic overtime calculation formula in Excel then this tutorials is for you. See the example below. To calculate overtime and pay associated with overtime, you can use the formulas explained on this page. Formula =(reg_hrs*rate)+(ot_hrs*rate*1.5) Explanation In formula in cell I5 is: =(F5*H5)+(G5*H5*1.5) How this formula works Note: it’s important …

Continue Reading

Calculate retirement date in Excel

by

If you need to calculate retirement date in Excel, then this tutorials is for you. To calculate a retirement date based on a birth date, you can use the EDATE function. See example below; Formula =EDATE(A1,12*60) Explanation In the example shown, the formula in D6 is: =EDATE(C6,12*60) How this formula works The EDATE function is fully automatic, and will …

Continue Reading

Get month from date in Excel

by

If you need to extract the month from a date, you can use the MONTH function. Note: The date in the  formula below must be in a form that Excel recognizes as a valid date.  Formula =MONTH(date) Explanation The MONTH function takes just one argument, the date from which you want to extract the month. …

Continue Reading

Add business days to date in Excel

by

If you need to add or subtract business days (workdays) to a date so that you can calculate a date in the future or past that skips weekends (and holidays),  then this tutorials is for you. The example below shows how to  Add business days to date in Excel. You can use the WORKDAY function. Holidays …

Continue Reading

Calculate years between dates in Excel

by

If you want to calculated the number of years between two dates,  then this tutorials is for you. See example below; To calculated the number of years between two dates, you can use the YEARFRAC function, which will return a decimal number representing the fraction of a year between two dates. Here are a few examples …

Continue Reading

Display Date is workday in Excel

by

This tutorial show how to Display Date is workday in Excel using the example below. To determine if a date is a workday or not, you can use a formula based on the WORKDAY function. =WORKDAY(date-1,1,holidays)=date Explanation of how this formula works In the example shown, the formula in C5 is: =WORKDAY(B5-1,1,holidays)=B5 which returns TRUE, since Monday, …

Continue Reading

Get last working day in month in Excel

by

If you need to determine the last working day of a given month and year, then this tutorials is for you. To get the last working day in a month, you can use the WORKDAY function together with the EOMONTH function. See example below: Formula =WORKDAY(EOMONTH(date)+1,-1) > In the example, the formula in C4 is: …

Continue Reading

Add days exclude certain days of week in Excel

by

It you need to add days and exclude certain days of week in Excel then this tutorials is for you. To add days to a date while excluding specific days (i.e. exclude Tuesdays and Thursdays, Wednesdays only, etc.) you can use the WORKDAY.INTL function with a special pattern code see the example below: Formula =WORKDAY.INTL(date,days,”pattern”) Explanation …

Continue Reading

Display Date is same month in Excel

by

This tutorial show how to Display Date is same month in Excel using the example below. To test two dates to see they both have the same month, you can do so with a simple formula that uses the MONTH function.  Formula =MONTH(date1)=MONTH(date2) Explanation of how this formula works In the example shown, the formula in …

Continue Reading

Get last weekday in month in Excel

by

This tutorial shows you how to find the last weekday of the month in Excel. To get the last weekday in a month (i.e. the last Saturday, the last Friday, the last Monday, etc) you can use a formula based on the EOMONTH and WEEKDAY functions. Formula =EOMONTH(date,0)+1-WEEKDAY(EOMONTH(date,0)+1-dow) Explanation First, this formula determines the first …

Continue Reading

Add days to date in Excel

by

It you need to add days to date in Excel then this tutorials is for you. See example below: To add a given number of years to a date, you can use the EDATE function. Formula =date+days Explanation In the example shown, the formula in D5 is: =B5+C5 How this formula works Dates in Excel are …

Continue Reading

Get first Monday before any date in Excel

by

To find the first Monday before any date you can use a formula based on the WEEKDAY function. Formula =date-WEEKDAY(date-2) In the example shown, the formula in C6 is: =B5-WEEKDAY(B5-2) Explanation If you imagine you have any random date and want to look back in time to find the nearest Monday, you can see that …

Continue Reading

Get last day of month in Excel

by

If you want to calculate the last day of a month based on a given date, then this tutorials is for you. The example below shows two different ways to achieve this. First, use the EOMONTH function. Formula =EOMONTH(date,0) Explanation In the example shown, the formula in cell B5 is: =EOMONTH(B5,0) The 2nd argument (months) …

Continue Reading

Add decimal hours to time in Excel

by

It you need to add decimal hours to time in Excel then this tutorials is for you. See example below. To add a given number of hours to a time, you can add hours divided by 24, or use the TIME function. Formula =time+(hours/24) Explanation In the example shown, the formula in D5 is: =B5+(C5/24) Note: make …

Continue Reading

Count birthdays by month in Excel

by

This tutorial show how to Count birthdays by month in Excel using the example below. To count the number of birthdays in a list, you can use a formula based on the SUMPRODUCT and MONTH functions. Formula =SUMPRODUCT(–(MONTH(birthday)=number)) Explanation of how this formula works In the example shown, E5 contains this formula: =SUMPRODUCT(–(MONTH(birthday)=D5)) This formula counts …

Continue Reading

Get date from day number in Excel

by

This tutorial show how to get date from day number in Excel using the example below. To get a real date from day number, or “nth day of year” you can use the DATE function. Formula =DATE(year,1,daynum) Explanation of how this formula works In the example shown, the formula in C5 is: =DATE(2015,1,B5) The DATE function build …

Continue Reading

Get fiscal year from date in Excel

by

To get a fiscal year from a date, you can use a formula based on the YEAR and MONTH functions. Formula =YEAR(date)+(MONTH(date)>=startmonth) Explanation In the example shown, the formula in D5 is: =YEAR(B5)+(MONTH(B5)>=C5) How this formula works By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal …

Continue Reading

Add decimal minutes to time in Excel

by

It you need to add decimal minutes to time in Excel then this tutorials is for you. See example below: To add a given number of minutes to a time, you can add minutes divided by 1440, or use the TIME function. Formula =time+(minutes/1440) Explanation In the example shown, the formula in D5 is: =B5+(C5/1440) Note: …

Continue Reading

Convert Unix time stamp to Excel date

by

This tutorial show how to Convert Unix time stamp to Excel date using the 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/86400)+DATE(1970,1,1) Explanation of how this formula works In the example shown, the formula in C5 is: =(B5/86400)+DATE(1970,1,1) The Unix …

Continue Reading

Get fiscal quarter from date in Excel

by

This tutorial show how to Get fiscal quarter from date in Excel. If you want to calculate the fiscal quarter from a date, and the fiscal quarter starts in a month other than January, you can use a formula based on the CHOOSE function. Formula =CHOOSE(MONTH(date),1,1,1,2,2,2,3,3,3,4,4,4) > Explanation Note: if you just need to calculate a …

Continue Reading

Add months to date in Excel

by

It you need to add months to date in Excel then this tutorials is for you. See example below: To add a given number of years to a date, you can use the EDATE function. Formula =EDATE(date,months) Explanation In the example shown, the formula in D5 is: =EDATE(B5,C5) How this formula works The EDATE function is …

Continue Reading

Convert time to time zone in Excel

by

This tutorial show how to Convert time to time zone in Excel using the example below. To convert a time from one time zone to another, you can use a formula that converts hours entered as whole numbers to the decimal values that Excel recognizes as times.  Formula =MOD(time+(hours/24),1) Explanation of how this formula works In …

Continue Reading

Get first day of previous month in Excel

by

To get the first day of the previous month for a given date, you can use a simple formula based on the EOMONTH function. Formula =EOMONTH(date,-2)+1 Explanation In the example shown, the formula in cell B5 is: =EOMONTH(B5,-2)+1 In the example shown, months is supplied as -2, which causes EOMONTH to return 4/30/2015. Then, 1 …

Continue Reading

Get day from date in Excel

by

This tutorial show how to Get day from date in Excel using the example below. If you need to extract the day from a date, you can use the DAY function. Formula =DAY(date) Explanation of how this formula works In the generic form of the formula above, the date must be in a form that Excel …

Continue Reading

Add workdays no weekends in Excel

by

It you need to add workdays no weekends in Excel then this tutorials is for you. See example below: Therefore, To add or subtract workdays days to a date, respecting holidays but assuming a 7-day workweek, you can you can use the WORKDAY.INTL function. Formula =WORKDAY.INTL(start_date,days,”0000000″, holidays) Explanation In the example, the formula in D6 is: =WORKDAY.INTL(B6,C6,”0000000″,holidays) …

Continue Reading

Convert text to date in Excel

by

This tutorial show how to Convert text to date in Excel using the example below. To convert text in an unrecognized date format to a proper Excel date, you can parse the text and assemble a proper date with a formula based on several functions: DATE, LEFT, MID, and RIGHT. Note: Before you go the formula route, see …

Continue Reading

Get first day of month in Excel

by

This tutorial shows how to get first day of month in Excel using the example below. To get the first day of the month for a given date, you can use a simple formula based on the DAY function. Formula =date-DAY(date)+1 In the example shown, the formula in cell C5 is: =B5-DAY(B5)+1 Explanation   The …

Continue Reading

Posts navigation

  • Previous
  • 1
  • 2
  • 3
  • 4
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Invoice status with nested if in Excel
  • IF with boolean logic in Excel
  • OR function Examples in Excel
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • XOR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get days before a date in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Quarter of Date in Excel
  • Convert time to time zone in Excel
  • Get first Monday before any date in Excel

Grouping

  • Group times into 3 hour buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign people to groups in Excel
  • Group numbers at uneven intervals in Excel

General

  • List sheet names with formula in Excel
  • Common Errors in Excel
  • Subtotal invoices by age in Excel
  • 44 Practical Excel IF function Examples
  • How to generate random date between two dates in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning