date() what is used for?

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,565
Hi
I am reading the article below. I know how to use Date() but I really can not see any use or befit of this function. The author gave some examples but again I do not see any use of these examples. Can you please give me examples where I really need to use date() function. Thank you so much.


<code style="font-family: "Courier new"; white-space: pre-wrap; color: rgb(69, 69, 69); font-weight: 600;">=DATE(2015, 5, 20)</code> - returns a serial number corresponding to 20-May-2015.
<code style="font-family: "Courier new"; white-space: pre-wrap; color: rgb(69, 69, 69); font-weight: 600;">=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)</code> - returns the first day of the current year and month.
<code style="font-family: "Courier new"; white-space: pre-wrap; color: rgb(69, 69, 69); font-weight: 600;">=DATE(2015, 5, 20)-5</code> - subtracts 5 days from May 20, 2015.

https://www.ablebits.com/office-addins-blog/2015/06/10/excel-date-functions/
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,072
Office Version
365
Platform
Windows
I use it all the time. One of the main uses i see is there is no abiguity to its use. In the UK if i see 1/2/18 its the 1st of February 2018. In the US its the 2nd Jan 2018. With DATE there is no problem. Its also useful in lots and lots of different date formulae where you want to place an actual date in the formula and not use a cell reference that refers to a date.
 

shaowu459

Active Member
Joined
Apr 26, 2018
Messages
468
Office Version
365
Platform
Windows
You have a date "2018-3-21" in A1, what's the date after 457 days?

=date(year(a1),month(a1),day(a1)+457)

after 34 month?

=date(year(a1),month(a1)+34,day(a1))

after 45 years?

=date(year(a1)+45,month(a1),day(a1))

after 4 years 5 month and 7 days?

=date(year(a1)+4,month(a1)+5,day(a1)+7)
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,923
Office Version
2013
Platform
Windows
It simply returns a date in the short date format of your system and the examples are to show you different methods that might be used to do that.
 

shaowu459

Active Member
Joined
Apr 26, 2018
Messages
468
Office Version
365
Platform
Windows
Another example, how to identify whether a year is a leap year? you can use following formula(year number stored in A1):

=IF(month(A1,2,29)=2,"Leap year","Not a leap year")
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
I think you mean:
=IF(MONTH(DATE(A1,2,29))=2,"Leap year","Not a leap year")
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
The Date Function is also useful when constructing a date when the DAY, MONTH, and YEAR are in different cells

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">DAY</td><td style=";">MONTH</td><td style=";">YEAR</td><td style=";">Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">17</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2018</td><td style="text-align: right;;">1/17/2018</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=DATE(<font color="Blue">C2,B2,A2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,007
Messages
5,465,976
Members
406,458
Latest member
Barboza Babcock

This Week's Hot Topics

Top