{"id":12152,"date":"2012-07-16T18:45:01","date_gmt":"2012-07-16T18:45:01","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=2150"},"modified":"2012-07-16T18:45:01","modified_gmt":"2012-07-16T18:45:01","slug":"date-formula","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/","title":{"rendered":"Excel Formula : DATE and TIME Formula"},"content":{"rendered":"\n\n
\n

Want to know about most of the Important Formulas in Excel? Read Excel Formulas Tutorial Page.<\/a> <\/p>\n<\/td>\n<\/tr>\n<\/table>\n

<\/a>
\n<\/a>
\n <\/p>\n

Click on the below links for to know about all these Formulas and how to use them<\/h3>\n

DATE<\/a> | DATEDIF<\/a> | DATEVALUE<\/a> | DAY<\/a> | DAYS360<\/a> | HOUR<\/a> | MINUTE<\/a> | MONTH<\/a> | NOW<\/a> | SECOND<\/a> | TIME<\/a> | TIMEVALUE<\/a> | TODAY<\/a> | WEEKDAY<\/a> | YEAR<\/a> <\/span>
\n<\/strong><\/p>\n

<\/p>\n

\n\n\n\n
DATE() Formula<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

This Formula returns a Valid Date in proper format by accepting 3 Numeric values as parameters as mentioned below in the Syntax. The best part of this formula is, It does not throw any error, even if you enter the Month Number as more than 12 (Month Can be only 12) or Day Number as more than 31. In such case it calculates the next valid date based on the input parameter.
\n<\/p>\n

Syntax:<\/h3>\n

=DATE(YEAR,MONTH,DAY)<\/strong>
\nWhere:<\/strong>
\nYEAR :<\/strong> Numeric Value for the Year. It accepts Two Digit of the Year format or Complete Year.
\nMONTH :<\/strong> Numeric Value for the Month.
\nDAY :<\/strong> Numeric Value for the Day.<\/p>\n

<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n\n
  Day  <\/th>\n  Month  <\/th>\n  Year  <\/th>\n  Date Returned by Formula  <\/th>\n  Formula Used   <\/th>\n<\/tr>\n
10<\/td>\n11<\/td>\n95<\/td>\nNovember 10, 1995<\/td>\n =DATE(A1,B1,C1)<\/font><\/td>\n<\/tr>\n
32<\/td>\n12<\/td>\n95<\/td>\nJanuary 1, 1996<\/td>\n =DATE(A2,B2,C2)<\/font><\/td>\n<\/tr>\n
30<\/td>\n13<\/td>\n95<\/td>\nJanuary 30, 1996<\/d><\/p>\n =DATE(E6,D6,C6)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

Let’s Discuss the above example for each row:<\/strong>
\n 
\nROW 1 : <\/strong>It has all valid parameters. It has all Day, Month and Year parameters as Valid one. Based on these parameters DATE() Formula returned corresponding Date.
\n 
\nROW 2: <\/strong>It has all valid parameters except the Day. In Day parameter 32 is passed. 32 can not be a date in any of the Month of any Year. Formula will not through any error. It will check the Month. Here month is 12 i.e. December. December has 31 days. So one day is extra from the last date of December. So the Formula will automatically consider this Date as 1st January. Here year is mentioned is 95. But now the date is adjusted in January, so the Year will also be shifted to 1996. This is the reason for the ROW 2, formula Returns January 1, 1996<\/strong>.
\n 
\nROW 3: <\/strong>Here Month is passed as 13. But in a Year only 12 months are possible and 12th Month is December, so 13th Month is considered as January Month of the Next Year. Therefore the formula returns as January 30, 1996<\/strong><\/p>\n

\n<\/a>
\n<\/p>\n

\n\n\n\n
DATEDIF() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

Basically DATEDIF<\/strong> calculates difference between two dates. The most interesting part of this Function is that you can calculate the difference between two dates by a given intervals. If i say Intervals<\/strong>, what does it mean? Interval means, In what interval do you actually want the difference between two dates like total difference in Months, or Years or Days etc.
\n 
\nSyntax for the DATEDIF() Function:<\/strong><\/p>\n

=DATEDIF(StartDate, EndDate, Interval) <\/span>
\n 
\nWhere:
\nStartDate:<\/strong> is the First Date
\nEndDate:<\/strong> is the Second Date
\nInterval:<\/strong> This is the format or Type in which the difference you want
\n <\/p>\n

Note:<\/h3>\n

First Date should not be later than Second Date. If First Date is later than Second Date then the Formula will return an Error.
\n 
\nFor Interval, we have few predefined Syntax, which you can use any one of them. Below is the list and Description for each of the Intervals<\/strong>
\n <\/p>\n\n\n<\/p>\n\n\n\n\n\n\n\n
Interval<\/span><\/td>\nMeaning<\/span><\/td>\nDescription<\/span><\/td>\n<\/tr>\n

<\/strong><\/p>\n

d<\/td>\nDay<\/td>\nReturns Total Number of Days between Two Dates<\/td>\n<\/tr>\n
m<\/td>\nMonth<\/td>\nReturns Total Number of Months between Two Dates<\/td>\n<\/tr>\n
y<\/td>\nyear<\/td>\nReturns Total Number of Years between Two Dates<\/td>\n<\/tr>\n
yd<\/td>\nDays Excluding Years<\/td>\nTotal Number of Days considering they are from the Same year.<\/td>\n<\/tr>\n
ym<\/td>\nMonths Excluding Years<\/td>\nTotal Number of Months considering they are from the Same year.<\/td>\n<\/tr>\n
md<\/td>\nNumber of Days Excluding Years and Month<\/td>\nTotal Number of Days considering they are from the Same Months and Same year.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Note:<\/h3>\n

1.<\/strong> If you are giving Dates and Interval Directly in your Formula then they both must be passed in DOUBLE QUOTES (“”) otherwise you can pass the reference directly.
\n 
\n=DATEDIF(StartDate,EndDate,”m”)<\/span>
\n<\/p>\n

How to Calculate Age using this Function:<\/h3>\n

Using this Function we can calculate Age of Some One just by Passing his\/her Birth date. In A1 Cell the Date of Birth is Kept. Considering that you can use the following Formula.
\n 
\n=DATEDIF(A1,TODAY(),”y”)&” Years “&DATEDIF(A1,TODAY(),”ym”)&” Months and “&DATEDIF(A1,TODAY(),”md”)&” Days”<\/span><\/p>\n

\n<\/a>
\n<\/p>\n

\n\n\n\n
DATEVALUE() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The DATEVALUE()<\/strong> Function Converts a Date, which is stored as Text in Excel, in to a Numeric Value or Serial Number, which Excel recognizes as a Date.
\nDATEVALUE function is helpful when you want to Filter or Sort some data based on Date Value which is stored as Text format in Excel.
\nSerial Number returned by this formula, can be seen as Date format by changing the Cell format as Date. <\/p>\n

<\/p>\n

Syntax:<\/h3>\n

=DATEVALUE(date_text)<\/strong>
\nWhere:<\/strong>
\ndate_text :<\/strong> It is required. It is a Text in Excel Date format or a Cell reference which is having Text in Excel Date format. For Example 01-Jan-2012 or 01\/01\/12 etc. date_text range is from 01-01-1900 to 31-12-9999<\/strong>. If you provide any date out of this range, then this Formula will return #VALUE<\/strong> Error.
\n<\/p>\n

Remarks:<\/h3>\n

Excel stores all the dates in a sequence. It starts from 01-01-1900 and this date is stored as 1, 02-01-1900 as 2 and so on. Sequence Number of 01-Jan-2008 39448. After 39447 days from 01-Jan-1900, date will be 01-Jan-2008.
\n<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n\n
  Date Text  <\/th>\n  Date Value  <\/th>\n  Formula used to get Date Value  <\/th>\n<\/tr>\n
25-DEC-99<\/td>\n36519<\/td>\n =DATEVALUE(A1)<\/font><\/td>\n<\/tr>\n
25\/12\/99<\/td>\n36519<\/td>\n =DATEVALUE(A2)<\/font><\/td>\n<\/tr>\n
10-01-99<\/td>\n36170<\/td>\n =DATEVALUE(A3)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

<\/a><\/p>\n

<\/p>\n

\n\n\n\n
DAY() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The DAY()<\/strong> Function returns the day of a Date or corresponding Serial Number. <\/p>\n

<\/p>\n

Syntax:<\/h3>\n

=DAY(serial_number)<\/strong>
\nWhere:<\/strong>
\nserial_number :<\/strong> It is required. Searial_Number is the date you want to find the Day. It can accept either a date or the corresponding date value, which you get from DATEVALUE Function.<\/a>
\n<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n\n
  Date  <\/th>\n  Day Returned by Formula  <\/th>\n  Formula used to get Day Value  <\/th>\n<\/tr>\n
25-DEC-99<\/td>\n25<\/td>\n =DAY(A1)<\/font><\/td>\n<\/tr>\n
31\/12\/99<\/td>\n31<\/td>\n =DAY(A2)<\/font><\/td>\n<\/tr>\n
10-01-99<\/td>\n10<\/td>\n =DAY(A3)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

Remarks: <\/h3>\n

Normally the result will be a number, but this can be formatted to show the actual day of the week by using Format,Cells,Number,Custom and using the code ddd or dddd.
\n
\nThe DAY() function<\/strong> can be used to calculate the name of the day for your birthday.<\/p>\n

<\/p>\n

\n

<\/img><\/p>\n<\/div>\n

<\/p>\n

<\/a>
\n<\/p>\n

\n\n\n\n
DAYS360() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The DAYS360() Function<\/strong> Returns the the number of days between two days. It calculates based on 360 (30 X 12 = 360)<\/strong> days in a year.<\/p>\n

<\/p>\n

Syntax:<\/h3>\n

DAYS360(start_date, end_date, [method])<\/strong>
\nWhere:<\/strong>
\nstart_date & end_date :<\/strong> These both arguement are required. These are dates between which you want the Number of Days. If start_date occurs after end_date, the DAYS360 function returns a negative number.
\n[method] :<\/strong> This is an optional Arguement. It specifies whether to use the U.S. or European method in the calculation. This is a Boolean Type arguement. If the Method is passed as TRUE, it means to use US calculation method and FALSE means to use European method in calculation.
\n<\/p>\n

What US and European Method of Calculation ? <\/h3>\n

US (Method – FALSE):<\/strong> If the starting date is the last day of a month, it becomes equal to the 30th day of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date becomes equal to the 30th day of the same month.
\n 
\n European (Method – TRUE):<\/strong> Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month.<\/p>\n

<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n\n
  Start Date  <\/th>\n  End Date  <\/th>\n  Days between Two Days by Formula  <\/th>\n  Formula used to Calculate  <\/th>\n<\/tr>\n
01-Jan-98<\/td>\n05-Jan-98<\/td>\n4<\/td>\n =DAYS360(B1,C1,TRUE)<\/font><\/td>\n<\/tr>\n
01-Jan-98<\/td>\n01-Feb-98<\/td>\n30<\/td>\n =DAYS360(B2,C2,TRUE)<\/font><\/td>\n<\/tr>\n
01-Jan-98<\/td>\n31-Mar-98<\/td>\n89<\/td>\n =DAYS360(B3,C3,TRUE)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

<\/a>
\n<\/p>\n

\n\n\n\n
HOUR() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The HOUR() Function<\/strong> returns Hour of a given Time.<\/p>\n

<\/p>\n

Syntax:<\/h3>\n

HOUR(serial_number)<\/strong>
\nWhere:<\/strong>
\nSerial_Number: <\/strong> Is the time in number format. Time may be entered as Text strings within quotation marks (“5:30 AM”) or may be in Decimal format like 0.25 which represents 06:00:00 AM.
\nResult is always shown from 0 to 23.
\n<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n
  Time Text  <\/th>\n  Hour  <\/th>\n  Formula used to get Hour  <\/th>\n<\/tr>\n
21:30<\/td>\n21<\/td>\n =HOUR(B1)<\/font><\/td>\n<\/tr>\n
0.25<\/td>\n6<\/td>\n =HOUR(C1)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

<\/a>
\n<\/p>\n

\n\n\n\n
MINUTE() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The MINUTE() Function<\/strong> returns Minute of a given Time.<\/p>\n

<\/p>\n

Syntax:<\/h3>\n

Minute(serial_number)<\/strong>
\nWhere:<\/strong>
\nSerial_Number: <\/strong> Is the time in number format. Time may be entered as Text strings within quotation marks (“5:30 AM”) or may be in Decimal format like 0.25 which represents 06:00:00 AM.
\nResult is always shown from 0 to 59.
\n<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n\n
  Time Text  <\/th>\n  Minute  <\/th>\n  Formula used to get Minute  <\/th>\n<\/tr>\n
17-07-2012 21:47<\/td>\n47<\/td>\n =MINUTE(B1)<\/font><\/td>\n<\/tr>\n
21:15:00<\/td>\n15<\/td>\n =MINUTE(C1)<\/font><\/td>\n<\/tr>\n
0.25<\/td>\n0<\/td>\n =MINUTE(D1)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

<\/a><\/p>\n

<\/p>\n

\n\n\n\n
MONTH() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The MONTH() Function<\/strong> returns Minute of a given Time.<\/p>\n

<\/p>\n

Syntax:<\/h3>\n

MONTH(serial_number)<\/strong>
\nWhere:<\/strong>
\nSerial_Number: <\/strong> is the date of the month you are trying to find. Date may be entered as Text strings like 01-Jan-1990 or may be the corresponding serial number of a valid date.
\n<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n
  Original Date   <\/th>\n  Month Returned by the Formula  <\/th>\n  Formula used to get Month  <\/th>\n<\/tr>\n
01-Jan-98<\/td>\n1<\/td>\n =MONTH(B1)<\/font><\/td>\n<\/tr>\n
01-Jan-98<\/td>\nJanuary<\/td>\n =MONTH(C1)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

Remarks:<\/h3>\n

Normally the result will be a number, but this can be formatted to show the actual month by using Format,Cells,Number,Custom and using the code mmm or mmmm.
\n<\/p>\n

<\/a><\/p>\n

<\/p>\n

\n\n\n\n
NOW() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The NOW() Function<\/strong> shows the current date and time. The result will be updated each time the worksheet is opened and every time an entry is made anywhere on the worksheet. You can change the date and time format for the cell by using the commands in the Number group of the Home tab on the Ribbon.<\/p>\n

<\/p>\n

Syntax:<\/h3>\n

NOW()<\/strong>
\nIt does not have any arguement<\/strong><\/p>\n

<\/p>\n

Remarks:<\/h3>\n

The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.
\n<\/p>\n

<\/a><\/p>\n

<\/p>\n

\n\n\n\n
SECOND() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The SECOND() Function<\/strong> returns Minute of a given Time.<\/p>\n

<\/p>\n

Syntax:<\/h3>\n

SECOND(serial_number)<\/strong>
\nWhere:<\/strong>
\nSerial_Number: <\/strong> is the time of which Second you are trying to find. Time may be entered as Text strings like 6:00 AM or may be the corresponding decimal value of a valid time.
\n<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n
  Original Time   <\/th>\n  Second Returned by the Formula  <\/th>\n  Formula used to get Second  <\/th>\n<\/tr>\n
4:48:18 PM<\/td>\n18<\/td>\n =SECOND(B1)<\/font><\/td>\n<\/tr>\n
4:48 PM<\/td>\n0<\/td>\n =SECOND(C1)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

Remarks:<\/h3>\n

Normally the result will be a number from 0 to 59.
\n<\/p>\n

<\/a><\/p>\n

<\/p>\n

\n\n\n\n
TIME() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

This Formula returns a Valid Time in proper format by accepting 3 Numeric values as parameters as mentioned below in the Syntax. The best part of this formula is, It does not throw any error, even if you enter the Second Number as more than 60 (Second Can be only 60) or Minute Number as more than 60. In such case it calculates the next valid time based on the input parameter.
\n<\/p>\n

Syntax:<\/h3>\n

=TIME(HOUR,MINUTE,SECOND)<\/strong>
\nWhere:<\/strong>
\nHOUR :<\/strong> Numeric Value for the Hour.
\nMINUTE :<\/strong> Numeric Value for the Minute.
\nSECOND :<\/strong> Numeric Value for the Second.<\/p>\n

<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n\n
  Hour  <\/th>\n  Minute  <\/th>\n  Second  <\/th>\n  Time Returned by Formula  <\/th>\n  Formula Used to get Time  <\/th>\n<\/tr>\n
14<\/td>\n30<\/td>\n59<\/td>\n14:30:59<\/td>\n =TIME(A1,B1,C1)<\/font><\/td>\n<\/tr>\n
14<\/td>\n62<\/td>\n59<\/td>\n3:02:59 PM<\/td>\n =TIME(A2,B2,C2)<\/font><\/td>\n<\/tr>\n
14<\/td>\n30<\/td>\n63<\/td>\n14:31:03<\/d><\/p>\n =TIME(E6,D6,C6)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

Let’s Discuss the above example for each row:<\/strong>
\n 
\nROW 1 : <\/strong>It has all valid parameters. It has all Second, Minute and Hour parameters as Valid one. Based on these parameters TIME() Formula returned corresponding Time.
\n 
\nROW 2: <\/strong>It has all valid parameters except the Minute. In Minute parameter 62 is passed. 62 can not be a Minute Value. Formula will not through any error. Minute is more than 60 here. Therefore 1 hour will be added in the hour and remaining minute will be kept. So the Formula will automatically consider this Time as 15:02:59<\/strong>.
\n 
\nROW 3: <\/strong>Here Second is passed as 63. But for Second more than 60 is not possible. Therefore 1 minute will be added in Minutes and and remaining second will be there in Second Part. There the time will be 14:30:59<\/strong><\/p>\n

<\/p>\n

<\/a><\/p>\n

<\/p>\n

\n\n\n\n
TIMEVALUE() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The TIMEVALUE()<\/strong> Function Converts a Time, which is stored as Text in Excel, in to a Numeric Value or Serial Number, which Excel recognizes as Time.
\nTIMEVALUE function is helpful when you want to Filter or Sort some data based on Time Value which is stored as Text format in Excel.
\nSerial Number returned by this formula, can be seen as Time format by changing the Cell format as Time. <\/p>\n

<\/p>\n

Syntax:<\/h3>\n

=TIMEVALUE(time_text)<\/strong>
\nWhere:<\/strong>
\ntime_text :<\/strong> It is required. It is a Text in Excel Time format or a Cell reference which is having Text in Excel Time format. For Example 14:30:59.
\n<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n\n
  Time Text  <\/th>\n  Time Value  <\/th>\n  Formula used to get Time Value  <\/th>\n<\/tr>\n
14:30:59<\/td>\n0.604849537<\/td>\n =TIMEVALUE(A1)<\/font><\/td>\n<\/tr>\n
14:30:59<\/td>\n14:30:59<\/td>\n =TIMEVALUE(A2)<\/font><\/td>\n<\/tr>\n
14:30:59<\/td>\n2:30:59 PM<\/td>\n =TIMEVALUE(A3)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

<\/a><\/p>\n

<\/p>\n

\n\n\n\n
TODAY() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The TODAY() Function<\/strong> shows the current date. The result will be updated each time the worksheet is opened and every time an entry is made anywhere on the worksheet. You can change the date format for the cell by using the commands in the Number group of the Home tab on the Ribbon.<\/p>\n

<\/p>\n

Syntax:<\/h3>\n

TODAY()<\/strong>
\nIt does not have any arguement<\/strong><\/p>\n

<\/p>\n

Remarks:<\/h3>\n

The results of the TODAY() function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.
\n<\/p>\n

<\/a><\/p>\n

<\/p>\n

\n\n\n\n
WEEKDAY() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The WEEKDAY()<\/strong> Function shows the day of the week from a date.<\/p>\n

<\/p>\n

Syntax:<\/h3>\n

=WEEKDAY(serial_number, [type])<\/strong>
\nWhere:<\/strong>
\nserial_number :<\/strong> It is required. It is date for which you want to know the Day.
\nType :<\/strong> It is an optional arguement. This is used to indicate the week day numbering system. There can be only following 3 values:<\/p>\n

1 : will set Sunday as 1 through to Saturday as 7
\n 2 : will set Monday as 1 through to Sunday as 7.
\n 3 : will set Monday as 0 through to Sunday as 6.\t\t\t<\/p>\n

Note :<\/strong> If you do not pass any value for this then the default value is considered as 1 by excel.<\/p>\n

<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n\n\n\n\n
  Date  <\/th>\n  Weekday Returned by Formula  <\/th>\n  Formula used to get Weekday  <\/th>\n<\/tr>\n
01-01-1998<\/td>\n5<\/td>\n =WEEKDAY(A1)<\/font><\/td>\n<\/tr>\n
01-01-1998<\/td>\n5<\/td>\n =WEEKDAY(A2)<\/font><\/td>\n<\/tr>\n
01-01-1998<\/td>\n5<\/td>\n =WEEKDAY(A3, 1)<\/font><\/td>\n<\/tr>\n
01-01-1998<\/td>\n4<\/td>\n =WEEKDAY(A4 2)<\/font><\/td>\n<\/tr>\n
01-01-1998<\/td>\n3<\/td>\n =WEEKDAY(A5 3)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

<\/a><\/p>\n

<\/p>\n

\n\n\n\n
YEAR() Formula<\/font><\/td>\nTOP^<\/a><\/font><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

What this Formula does ?<\/h3>\n

The YEAR() Function<\/strong> returns Minute of a given Time.<\/p>\n

<\/p>\n

Syntax:<\/h3>\n

YEAR(serial_number)<\/strong>
\nWhere:<\/strong>
\nSerial_Number: <\/strong> is the date of the YEAR you are trying to find. Date may be entered as Text strings like 01-Jan-1990 or may be the corresponding serial number of a valid date.
\n<\/p>\n

Example:<\/h3>\n

<\/p>\n

\n\n\n\n
  Original Date   <\/th>\n  Year Returned by the Formula  <\/th>\n  Formula used to get Year  <\/th>\n<\/tr>\n
01-Jan-98<\/td>\n1998<\/td>\n =YEAR(B1)<\/font><\/td>\n<\/tr>\n<\/table>\n<\/div>\n

<\/p>\n

Remarks:<\/h3>\n

Normally the result will be a number, but this can be formatted to show the actual Year by using Format,Cells,Number,Custom and using the code mmm or mmmm.<\/p>\n

<\/p>\n\n\n
\n<\/p>\n

\nWant to Know Syntax and How to use most of the Important Formulas? Then Read Excel Formulas Tutorial Page.<\/a>
\n<\/h3>\n

\n<\/td>\n<\/tr>\n<\/table>\n<\/span>","protected":false},"excerpt":{"rendered":"

Want to know about most of the Important Formulas in Excel? Read Excel Formulas Tutorial Page.   Click on the below links for to know about all these Formulas and how to use them DATE | DATEDIF | DATEVALUE | DAY | DAYS360 | HOUR | MINUTE | MONTH | NOW | SECOND | TIME […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1672,1673],"tags":[],"class_list":["post-12152","post","type-post","status-publish","format-standard","hentry","category-excel-formula","category-excel-functions"],"yoast_head":"\nExcel Formula : DATE and TIME Formula - Let's excel in Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel Formula : DATE and TIME Formula\" \/>\n<meta property=\"og:description\" content=\"Want to know about most of the Important Formulas in Excel? Read Excel Formulas Tutorial Page.   Click on the below links for to know about all these Formulas and how to use them DATE | DATEDIF | DATEVALUE | DAY | DAYS360 | HOUR | MINUTE | MONTH | NOW | SECOND | TIME […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2012-07-16T18:45:01+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/learnexcelmacro.com\/wp\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"13 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Excel Formula : DATE and TIME Formula\",\"datePublished\":\"2012-07-16T18:45:01+00:00\",\"dateModified\":\"2012-07-16T18:45:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/\"},\"wordCount\":2623,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png\",\"articleSection\":[\"Excel Formula\",\"Excel Functions\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/\",\"name\":\"Excel Formula : DATE and TIME Formula - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png\",\"datePublished\":\"2012-07-16T18:45:01+00:00\",\"dateModified\":\"2012-07-16T18:45:01+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Formula\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-formula\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Excel Formula : DATE and TIME Formula\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Excel Formula : DATE and TIME Formula - Let's excel in Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/","og_locale":"en_US","og_type":"article","og_title":"Excel Formula : DATE and TIME Formula","og_description":"Want to know about most of the Important Formulas in Excel? Read Excel Formulas Tutorial Page.   Click on the below links for to know about all these Formulas and how to use them DATE | DATEDIF | DATEVALUE | DAY | DAYS360 | HOUR | MINUTE | MONTH | NOW | SECOND | TIME […]","og_url":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2012-07-16T18:45:01+00:00","og_image":[{"url":"http:\/\/learnexcelmacro.com\/wp\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Excel Formula : DATE and TIME Formula","datePublished":"2012-07-16T18:45:01+00:00","dateModified":"2012-07-16T18:45:01+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/"},"wordCount":2623,"commentCount":3,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png","articleSection":["Excel Formula","Excel Functions"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/","url":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/","name":"Excel Formula : DATE and TIME Formula - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png","datePublished":"2012-07-16T18:45:01+00:00","dateModified":"2012-07-16T18:45:01+00:00","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/07\/birthday.png"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2012\/07\/date-formula\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Formula","item":"https:\/\/vmlogger.com\/excel\/excel-formula\/"},{"@type":"ListItem","position":3,"name":"Excel Formula : DATE and TIME Formula"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12152"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12152"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12152\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}