Date and Time Formula in Excel

Date Function

It is an MS Excel DATE function creates a valid date from individual year, month, and day workings. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.

Note :-  It is created a date with year, month, and day in MS Excel.

Example :-

Year – It is number for year.

Month – It is number for month.

Day – It is umber for day.

Syntax :- =DATE (year, month, day) Enter

DATEDIF Function

It is an Excel DATEDIF function returns the variance between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "Compatibility" function that comes from Lotus 1-2-3. For details nameless, it is only documented in Excel 2000, but you can use it in your formulas in all MS Excel versions since that time.

Note: - In MS Excel won't help you fill out the influences for DATEDIF like other functions, but it will be work when configured acceptably. It is get days, months, or years between two dates.

Example :-  

Start_Date :- Start date in Excel date serial number format.

End_Date :- End date in Excel date serial number format.

Unit: - The time unit to use (years, months, or days).

Syntax :- 

=DATEDIF (Start_Date, End_ate, unit) Enter

Note :-If you want to Date of birth show up you can see very easily to this formula because it can created between Dates deferent like Years, Months, Days  it is very easily formula.

DATEVALUE Function

It is an Excel DATEVALUE function converts a date represented as text into a proper Excel date. For example, the formula =DATEVALUE("09/30/2020") returns a serial number in the Excel date system that epitomizes December 31, 2020. Proper Excel dates are more useful than text dates since they can be directly manipulated with formulas and pivot tables to create sum-ups and perform other date-based analytics.

Note :- 

It is convert a date in text format to a valid date.

Example:-

=Date_text - A valid date in text format.

=DATEVALUE ("09/30/2020") Enter

 Syntax  :- =DATEVALUE (Date_Text) Enter

DAY Function

It is an Excel DAY function returns the day of the month as a (number between 1 to 31 from a given date). You can use the DAY function to abstract a day number from a date into a cell. You can also use the DAY function to abstract and feed a day value into another function, like the DATE function.

Note :-

A number (1-31) representing the day component in a date.

Syntax :- =DAY (date) Enter

Example :- =DAY(B2) Enter

 DAYS360 Function

It is an Excel DAYS360 function returns the number of days between two dates based on a 360-day year. Calculations based on a 360-day year comes from confident secretarial calculations where all 12 months are reflected to have 30 days.

Note :- It's get days between 2 dates in a 360-day year a number representing days.

Example :-

=DAYS360(B2,C2) Enter

Start_Date – It is start date.

End_Date – It is end date.

Method – {optional } The type of day count basis to use. FALSE (default) is US method, TRUE is European method.

Syntax  :- =DAYS360 (start_date, end_date, [{method}) Enter

 

EDATE Function

It is an Excel EDATE function returns a date on the same day of the  month, next months in the past or future. What will come on next month .You can use EDATE to calculate expiration dates, maturity dates, and other due dates? Use a positive value for months to get a date in the future, and a negative value for dates in the past.

Note :-if you want to see date next months in future or past and new date as Excel serial number, you can.

Syntax :-

=EDATE (start_date, months) Enter

Example :- =EDATE(B2,C2) Enter

Start_date - Start date as a valid Excel date.

Months - Number of months before or after start_date.

EOMONTH Function

It is an Excel EOMONTH function returns the last day of the month, next months in the past or future. You can use EDATE to calculate termination dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for months to get a date in the future, and a negative value to get a date in the earlier.

Notes :- it function get last day of month n months in future or past last day of month date.

Syntax : - =EOMONTH (start_date, months) Enter

Example :-=EOMONTH(B2,C2) Enter

Start_Date - A date that represents the start date in a valid Excel serial number format.

Months - The number of months before or after start_date.

HOUR Function

It is an MS Excel HOUR function returns the hour component of a time as a number between 0-20. For example, with a time of 5:30 AM, HOUR will return 5. You can use the HOUR function to excerpt the hour into a cell, or feed the result into alternative formula, like the TIME function/Formula.

Note :- it is get the hour as a number (0-20) from a Time a number between 0 and 20.

Syntax

=HOUR (serial_number) Enter

Example :- =HOUR(B2) Enter

serial_number - A valid Excel time.

MINUTE Function

It is an Excel MINUTE function extracts the minute factor of a time as a number between 0-59. For example, with a time of 10:45 AM, minute will return 45. You can use the MINUTE function to excerpt the minute into a cell, or fodder the result into another function like the TIME function/Formula.

Note :- It is get minute as a number (0-59) from time number between 0 and 59.

Syntax

=MINUTE (serial_number) Enter

Example:-=MINUTE(B2) Enter

Serial_number - A valid date or time.

MONTH Function

 

It is an Excel MONTH function excerpts the month from a given date as number between 1 to 12. You can use the MONTH function to excerpt a month number from a date into a cell, or to feed a month number into another function like the DATE function/Formula.

Note :- It is get month as a number (1-12) from a date a number between 1 and 12.

Syntax

=MONTH (date) Enter

Example :-=MONTH(B2) Enter

Date - A valid Excel date.

NETWORKDAYS Function

It is an Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally except a list of holidays complete as dates.

 

NETWORKDAYS includes both the start date and end date when calculating work days. This means if you give NETWORKDAYS the same date for start date and end date, and the date is not a weekend or holiday, it will return 1.

 

NETWORKDAYS can also optionally take into account holidays. For the holiday’s disagreement, supply a range that contains holiday dates. These are also treated as non-working days and will not be involved in the result.

Syntax :-

=NETWORKDAYS (start_date, end_date, {holidays}) Enter

Example :-=NETWORKDAYS(B2,C2,$F$2:$F$3) Enter

Start_date - The start date.

End_date - The end date.

Holidays - {optional} A list of non-work days as dates.

NOW Function

It is an Excel NOW function returns the current date and time, updated continuously when a worksheet is different or opened. The NOW function takes no arguments. You can format the value returned by NOW as a date, or as a date with time by applying a number format.

Note: - It is get the current date and time a serial number representing a particular date and time in Excel.

Syntax :-

=NOW () Enter

Shortcut key function :-CTRL +SHIFT KEY +   (It is show the system time )

Shortcut key function :- CTRL+ :  (It is show the system date)

Second Function

It is an Excel SECOND function returns the second component of a time as a number between 0-59. For example, with a time of 10:30:15 AM, second will return 15. You can use the SECOND function to extract the second into a cell, or feed the result into another formula, it’s like the TIME function in MS excel.     

Syntax :-=SECOND (serial_number) Enter

Example :- =SECOND(C2) Enter

 Note :- Serial_number –  It is a  valid time in a format Excel recognizes.

TIME Function

It is an Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. It is TIME function is useful when you want to assemble a proper time inside another formula in MS Excel.

Notes :-

It is create a time with hours, minutes, and seconds and a decimal number representing a particular time in MS Excel.

Syntax :- =TIME (hour, minute, second) Enter

Example :- =TIME(C2,D2,E2) Enter

Hour – it is hour for the time you wish to create.

Minute – It is minute for the time you wish to create.

Second –It is second for the time you wish to create.

TODAY Function

It is an Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. It is TODAY function takes no arguments. You can format the value returned by TODAY using any standard date format. If you need current date and time use the NOW Functions in MS excel.

Notes 

it is get the current date valid of MS Excel date.

Syntax :- =TODAY ()  Enter

Example :- =TODAY() Enter

=TODAY()                       current date

=TODAY()-7                   one week ago

=TODAY()+7                  one week later

keyboard shortcut Ctrl + ;

WEEKDAY Function

It is an Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday. You can use the WEEKDAY function inside other formulas to check the day of week and react as needed in MS Excel.

Notes: -It is get the day of the week as a number a number between 0 and 7.

Syntax :- =WEEKDAY (serial_number, {return_type}) Enter

Example :-=WEEKDAY(E2) Enter

It is WEEKDAY accepts a date and returns a number between 1-7 representing the day of week for that date. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, as shown in the table below in MS Excel:

WEEKNUM Function

It is WEEKNUM Function is an Excel DATE and TIME Function. Its cheat sheet covers 100s of functions that are critical to know as an Excel analyses. It will return the week number of a definite date. It function will return an integer that represents a week number from 1 to 52 weeks of the year.

Note :-   It is “return type” argument resembles to different days you can choose as the start day. The changes the week number based on what day of the week that the “return type” sets as the first day of the year.

 

Syntax :-=WEEKNUM(serial_number, [return_type]) Enter

Example :- =WEEKNUM(E2,17) Enter

WORKDAY Function

Its function returns a day that is some number of workdays into the future or before some date. A “WORKDAY” is days excluding weekends and any holidays that are specified.

 Notes :- The function requires two arguments and has an optional third.

Get a date n working days in the future or past

A serial number representing a particular date in Excel.

Syntax :- =WORKDAY(start_date, days, [holidays]) Enter

Example :- =WORKDAY(E2,F2,E$12:E$13) Enter

Start _Date –: this required argument is the date from when you want to count the number of workdays

Days – : this required argument is the number of days from the start date you want a count of. Using a negative number will give you the date that many workdays before your start date.

Holidays – : this argument is optional. This allows you to add holiday dates in the formula for the WORKDAY function to skip along with weekends.