How to Calculate Joining Date in Excel with Writing Patterns Using
If you are a human resource manager, calculating the joining date of employees can be a daunting task, especially if they have different writing patterns. Fortunately, Excel can make this task easier for you. In this article, you will learn how to use Excel to calculate the joining date of employees with different writing patterns.
Step 1: Prepare your data
Before you can start calculating the joining date of your employees, you need to have a proper list of their names, joining dates, and writing patterns. Make sure that your data is clean and free of errors.
Step 2: Create a column for the join date
Next, you need to create a new column for the joining date of your employees. To do this, simply select the column to the right of your employee names and label it ‘Join Date’.
Step 3: Enter the join date
Now that you have a column for the join date, you can start entering the joining dates of your employees. Make sure that the dates are in the correct format (e.g. dd/mm/yyyy or mm/dd/yyyy).
Step 4: Create a column for the writing pattern
In order to calculate the joining date for employees with different writing patterns, you need to create a new column for their writing pattern. This column should be to the right of the join date column.
Step 5: Enter the writing pattern
Once you have created the column for the writing pattern, you can start entering the writing pattern of each employee. This can be as simple as ‘ddmmyyyy’ or ‘mmddyyyy’.
Step 6: Convert the join date to a serial number
To calculate the joining date for employees with different writing patterns, you need to convert the join date to a serial number. To do this, select the cell to the right of the join date cell and enter the following formula: =DATEVALUE(cell reference).
Step 7: Convert the serial number to a date
Now that you have converted the join date to a serial number, you need to convert the serial number back to a date. To do this, select the cell to the right of the serial number cell and enter the following formula: =TEXT(cell reference, writing pattern).
Step 8: Calculate the number of days between the join date and the current date
To calculate the number of days between the join date and the current date, select the cell to the right of the date cell and enter the following formula: =TODAY()-cell reference.
Step 9: Format the number of days
To make the number of days more readable, you can format the cell as a number with zero decimal places.
Step 10: Calculate the number of years of service
To calculate the number of years of service, select the cell to the right of the number of days cell and enter the following formula: =YEARFRAC(join date cell reference, current date cell reference).
Step 11: Format the number of years of service
To make the number of years of service more readable, you can format the cell as a number with zero decimal places.
Step 12: Calculate the number of months of service
To calculate the number of months of service, select the cell to the right of the number of years of service cell and enter the following formula: =DATEDIF(join date cell reference, current date cell reference, ’m’).
Step 13: Format the number of months of service
To make the number of months of service more readable, you can format the cell as a number with zero decimal places.
Step 14: Calculate the number of days of service
To calculate the number of days of service, select the cell to the right of the number of months of service cell and enter the following formula: =DATEDIF(join date cell reference, current date cell reference, ’d’).
Step 15: Format the number of days of service
To make the number of days of service more readable, you can format the cell as a number with zero decimal places.
Step 16: Calculate the days remaining for the next anniversary
To calculate the number of days remaining for the next anniversary, select the cell to the right of the number of days of service cell and enter the following formula: =DATEDIF(current date cell reference, DATE(YEAR(current date cell reference)+1,MONTH(join date cell reference),DAY(join date cell reference)), ’d’).
Step 17: Format the days remaining for the next anniversary
To make the number of days remaining for the next anniversary more readable, you can format the cell as a number with zero decimal places.
Step 18: Calculate the next anniversary date
To calculate the next anniversary date, select the cell to the right of the days remaining for the next anniversary cell and enter the following formula: =DATE(YEAR(current date cell reference)+1,MONTH(join date cell reference),DAY(join date cell reference)).
Step 19: Format the next anniversary date
To make the next anniversary date more readable, you can format the cell as a date.
Step 20: Review and update your data
Now that you have calculated the joining date and other important information for your employees, you should review your data and make any necessary updates. This will ensure that your data is accurate and up-to-date.
