5 Date And Time Functions For Salary Calculation In Excel

In a previous article, Buffcom.net explored some functions in Excel such as VLOOKUP and HLOOKUP. In this article, we introduce to our readers 5 date functions commonly used in salary calculation on Microsoft Excel 2016. Let’s learn and remember them with Buffcom.net.

DATEDIF Function

This is the most commonly used date function in salary calculation, used to count the number of days, months, years, etc. between two specific dates.

Formula: =DATEDIF(start_date,end_date,unit)

Where:

• start_date: the start date
• end_date: the end date
• unit: the type of information you want to receive, including y, m, d, md, ym, yd (remember to place in double quotes)
• y: the total number of years rounded down from the start date to the end date
• m: the total number of months rounded down from the start date to the end date
• d: the total number of days from the start date to the end date (equivalent to the DAYS function)
• md: the number of days between the start date and the end date, ignoring the number of months and years
• ym: the number of months between the start date and the end date, ignoring the number of days and years
• yd: the number of days between the start date and the end date, ignoring the number of years

For example, we can calculate the time period between two dates of 1/6/2020 and 28/7/2021, which is 422 days (result when using the d unit).

TODAY Function

This is a simple yet extremely useful function when combined with other functions in formulas, showing the current date and always updating every time you open the Excel spreadsheet.

Syntax: =TODAY()

Similar to the TODAY function, there are other functions used to update the month, year, etc. such as the MONTH and YEAR functions.

NETWORKDAYS Function

Used to calculate the number of working days in a period, excluding holidays. (by default, Saturday and Sunday are considered holidays)

Syntax: =NETWORKDAYS(start_date,end_date,holidays)

Where:

• start_date: the start date
• end_date: the end date
• holidays: a range of values representing holidays or non-working days on weekends. When leaving the holidays value blank, the period calculated includes only working days except for Saturday and Sunday.

However, when the company needs to calculate the number of working days but the weekly holiday is not on the weekend (Saturday, Sunday), we use the NETWORKDAYS.INTL function.

Syntax: =NETWORKDAYS.INTL(start_date,end_date,weekend,holidays)

Weekends represent regular weekly days off, interpreted as a sequence of 7 characters in double quotes with 1 as a day off and 0 as a workday. If the weekends are left blank, Excel defaults to Saturday and Sunday as regular days off. For example, “0100011” represents the weekly days off on Tuesday, Saturday, and Sunday.

The WORKDAY Function

When you hire employees for a certain number of days, the WORKDAY function helps you calculate the last working day for the employee, based on the agreed-upon number of workdays.

Syntax: =WORKDAY(start_date,days,holidays)

Where:

• start_date: the starting date of work
• days: number of workdays
• holiday: similar to NETWORKDAYS function

Similar to the NETWORKDAYS and NETWORKDAYS.INTL functions, the WORKDAY.INTL function has a similar meaning and syntax.

Syntax: =WORKDAY.INTL(start_date,days,weekend,holidays)

The WEEKDAY Function

The WEEKDAY function is a date function that returns the day of the week for a given date. By default, Excel defines Sunday as 1 and Saturday as 7.

Syntax: =WEEKDAY(serial_number,return_type)

Where: serial_number is the date given in Number or Date format, and return_type is how you want to display the result. If you enter:

1 or leave it blank, the week starts on Sunday (result of 1) and ends on Saturday (result of 7)
2: the week starts on Monday (result of 1) and ends on Sunday (result of 7)
3: the week starts on Monday (result of 0) and ends on Sunday (result of 6)
Additionally, the WEEKNUM function returns the week number of a given date in a year.

Syntax: =WEEKNUM(serial_number,return_type)

Summary of Date Functions

In Excel payroll, in addition to date functions, you will need to add other conditional functions and calculations to complete your own formula for your company.

In this article, Buffcom.net introduced the top 5 date functions for Excel payroll:

• DATEDIF Function
• TODAY Function
• NETWORKDAYS Function
• WORKDAY Function
• WEEKDAY Function
  Also, if your job is closely related to Excel but the software does not meet your needs, contact Buffcom.net to choose the necessary product!