DATE function in Excel

Excel Tutorials for beginners, Intermediates and experts.

How to generate random date between two dates in Excel

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… read more »

How to calculate nth day of year in Excel

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

Convert Unix time stamp to Excel date

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… read more »

Convert text to date in Excel

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… read more »

Get date from day number in Excel

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… read more »

Sidebar