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

Data Analysis

  • Conditional Formatting Rules in Excel
  • How To Load Analysis ToolPak in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Reverse List in Excel

References

  • Left Lookup in Excel
  • Perform case-sensitive Lookup in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to use Excel TRANSPOSE Function
  • How to get last column number in range in Excel

Data Validations

  • Data validation must not exist in list
  • Prevent invalid data entering in specific cells
  • Excel Data validation don’t exceed total
  • Excel Data validation allow uppercase only
  • Excel Data validation must begin with

Tag: DATE function

DATE function: Description, Usage, Syntax, Examples and Explanation

by

What is DATE function in Excel? DATE function is one of Date and Time functions in Microsoft Office Excel that when you need to take three separate values and combine them to form a date. Note: Microsoft Excel DATE function returns the serial date value for a date. Syntax of DATE function The syntax for the DATE function in …

Continue Reading

Convert date to Julian format in Excel

by

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 …

Continue Reading

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

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

by

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 …

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

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 determine year is a leap year in Excel

by

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 …

Continue Reading

How to generate series of dates increment by 1 year from a single start date in Excel

by

If need to generate a dynamic series of dates with a formula that increase by one year from a single start date, you can do so with a formula that uses the DAY, MONTH, YEAR, and DATE functions. Formula =DATE(YEAR(date)+1,MONTH(date),DAY(date)) Explanation In the example, B6 is the hard-coded start date and the formula in B7 …

Continue Reading

How to generate random date between two dates in Excel

by

To generate random dates between two dates, you can use the RANDBETWEEN function, together with the DATE function. See example below: Formula =RANDBETWEEN(date1,date2) Explanation In the example shown, the formula in B5 is: =RANDBETWEEN(DATE(2016,1,1),DATE(2016,12,31)) This formula is then copied down from B5 to B11. The result is random dates between Jan 1, 2016 and Dec …

Continue Reading

Highlight dates between in Excel

by

This tutorial shows how to Highlight dates between in Excel using the example below; Formula =AND(A1>=date1,A1<=date2) Explanation If you want to highlight dates between two dates with conditional formatting, you can use a simple formula that uses the AND and DATE functions together. For example, if you have dates in the range B4:G11, and want to …

Continue Reading

Highlight rows with dates between In Excel

by

This tutorial shows how to Highlight rows with dates between In Excel using the example below; Formula =AND($A1>=start,$A1<=end) Explanation If you want to highlight rows that contain dates between two dates with conditional formatting, you can use a formula based on the AND and DATE functions. In the example, shown, the range B5:E11 has a …

Continue Reading

Add years to date in Excel

by

If you need to add years to date in Excel then this tutorials is for you. See the example below . To add a given number of years to a date, you can use a formula based on the DATE function, with help from the YEAR, MONTH, and DAY functions. Formula =DATE(YEAR(date)+years,MONTH(date),DAY(date)) Note: if you need …

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

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

How to calculate Day of the Year in Excel

by

An easy formula that returns the day of the year for a given date. There’s no built-in function in Excel that can do this. 1. To get the year of a date, use the YEAR function. 2. Use the DATE function below to return January 1, 2016. The DATE function accepts three arguments: year, month and day. 3. The …

Continue Reading

Excel Date & Time Functions Example

by

To enter a date in Excel, use the “/” or “-” characters. To enter a time, use the “:” (colon). You can also enter a date and a time in one cell. Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings. Learn more about Date and Time formats. Year, …

Continue Reading

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
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • OR function Examples in Excel
  • How to use IFS function in Excel

Date Time

  • How to calculate Next working/business day in Excel
  • Add days to date in Excel
  • Display Date is same month in Excel
  • Basic Overtime Calculation Formula in Excel
  • How to get Weekdays, Working days between Two Dates in Excel

Grouping

  • Categorize text with keywords in Excel
  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel
  • Group numbers at uneven intervals in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Currency vs Accounting Format in Excel
  • How to calculate percent variance in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • How to Create Calendar in Excel
  • How to fill cell ranges with random number from fixed set of options in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning