How to calculate length of service
This example looks very simple. For example, I joined the company in 2000 and this year in 2015, the result should be 15 years. Some users may think that it is better to use the YEAR() function to take two years and subtract them. However, we should consider the situation from 2015.12.1 to 2016.5.1, which is only 5 months and less than the whole year, and cannot be counted as working age. Of course, the age is the same. When you are not old, you cannot be counted as the whole year. Therefore, we deny the algorithm of year subtraction.
1. Convert the number of adults by days
Similarly, if I calculate the number of days, calculate the difference between the two days, divide it by 365, and then round it, can I get the year.
The following code can be directly copied to the formula editor,Replace the end time and start time fields.
{"text":"INT(DAYS(DATE(终止时间),DATE(起始时间))/365)","marks":[{"from":{"line":0,"ch":14,"sticky":null},"to":{"line":0,"ch":20,"sticky":null},"value":"dateField_ksj7dsfg","invalid":false},{"from":{"line":0,"ch":27,"sticky":null},"to":{"line":0,"ch":33,"sticky":null},"value":"dateField_ksj7dsfe","invalid":false}],"isCmData":true}
Calculating length of service
2. Convert the number of adults by timestamp
Similarly, witty friends may also directly start from the timestamp, directly calculate the difference between the two timestamps, and then convert and integer them in milliseconds.
The following code can be directly copied to the formula editor,Replace the end time and start time fields.
{"text":"IF(OR(ISEMPTY(起始时间),ISEMPTY(终止时间)),\"\",INT((终止时间-起始时间)/1000/60/60/24/365))","marks":[{"from":{"line":0,"ch":14,"sticky":null},"to":{"line":0,"ch":20,"sticky":null},"value":"dateField_ksj7dsfe","invalid":false},{"from":{"line":0,"ch":30,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"dateField_ksj7dsfg","invalid":false},{"from":{"line":0,"ch":47,"sticky":null},"to":{"line":0,"ch":53,"sticky":null},"value":"dateField_ksj7dsfg","invalid":false},{"from":{"line":0,"ch":54,"sticky":null},"to":{"line":0,"ch":60,"sticky":null},"value":"dateField_ksj7dsfe","invalid":false}],"isCmData":true}
Calculating length of service
3. Accurate calculation method
Here, careful friends will find that no matter which algorithm is used, it is inevitably inaccurate. 365 days is only the approximate value of a year, or 366 days. Although the possibility of affecting the results is relatively low, some users with high requirements still feel bad. The following ultimate solution involves the definition of timestamp itself, which is difficult to understand. It is not required to master it here. Friends with conditions can study it.
Functions used: DATE(),YEAR(),MONTH(),DAY()
Yes, yes, these four functions are used to accurately calculate the whole year with an accuracy of milliseconds (in timestamp units). Here is a concept. The value of the timestamp is 0:00:00 on January 1, 1970. At the beginning of the article, the author said that the time difference is calculated by year subtraction. Here, we consider reducing the month and DATE, and then using DATE() to synthesize the time object.
In one case, the start time is January 1, 2015; The end time is October 1, 2015. These two subtracts, and the result should be DATE(0,10,0). It is worth mentioning here that the 0th can be regarded as the last day of last month, and the month can be minus 1. What about 0 years? We know that there is no concept of "0 year" between 1 AD and 1 BC. Like EXCEL, 0-99 means 1900-1999. from 100, the time value obtained by YEAR(DATE(100,1,1) is 100 years of public yuan, and there will be no special time later. Therefore, if the difference between the two years before and after is 5 years, the YEAR value should be 1905, which is totally wrong.
I write functions like this:
YEAR: YEAR(DATE (end time)-YEAR(DATE (initial time) +100
MONTH: MONTH(DATE)-MONTH(DATE (initial time) +1
DATE: DAY(DATE (end time)-DAY(DATE (initial time) +2
I don't think most users and friends can understand that year +100 is understood, because if the ending year-initial year is less than 100, the obtained value is 1900-1999. Adding 100 here can be subtracted at the end. So why is the month +1 and the date +2?
Let's imagine: from October 1, 2015 to September 30, 2016, it should be a whole year. If you do not add 1 and 2 at the end of the calculation, the result should be DATE(101,-1,-1), automatic borrowing, and the result should be "November 30, 100", year-100, and finally 11 months and 30 days.
Where does the error come from?
We know that there are 12 months in a year. If the time difference between the two is 0 months, the actual time does not exist in the 0 month situation. The year-1 is automatically borrowed from the year, and the month changes to December, the number of days is the same. Therefore, in order to avoid such borrowing, we give each month difference and day difference +1, making the original 12 decimal system from 0 to 11 become the 12 decimal system from 1 to 12. Finally, consider that the whole year is generally from October 1, 2015 to September 30, 2016, instead of October 1 to October 1, the following year.
Therefore, add 1 to the last number of days, so the final formula is:
The following code can be directly copied to the formula editor,Replace the end time and start time fields.
{"text":"IF(OR(ISEMPTY(起始时间),ISEMPTY(终止时间)),\"\",YEAR(DATE(YEAR(DATE(终止时间))-YEAR(DATE(起始时间))+100,MONTH(DATE(终止时间))-MONTH(DATE(起始时间))+1,DAY(DATE(终止时间))-DAY(DATE(起始时间))+2))-100)","marks":[{"from":{"line":0,"ch":14,"sticky":null},"to":{"line":0,"ch":20,"sticky":null},"value":"dateField_ksj7dsfe","invalid":false},{"from":{"line":0,"ch":30,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"dateField_ksj7dsfg","invalid":false},{"from":{"line":0,"ch":62,"sticky":null},"to":{"line":0,"ch":68,"sticky":null},"value":"dateField_ksj7dsfg","invalid":false},{"from":{"line":0,"ch":81,"sticky":null},"to":{"line":0,"ch":87,"sticky":null},"value":"dateField_ksj7dsfe","invalid":false},{"from":{"line":0,"ch":105,"sticky":null},"to":{"line":0,"ch":111,"sticky":null},"value":"dateField_ksj7dsfg","invalid":false},{"from":{"line":0,"ch":125,"sticky":null},"to":{"line":0,"ch":131,"sticky":null},"value":"dateField_ksj7dsfe","invalid":false},{"from":{"line":0,"ch":145,"sticky":null},"to":{"line":0,"ch":151,"sticky":null},"value":"dateField_ksj7dsfg","invalid":false},{"from":{"line":0,"ch":163,"sticky":null},"to":{"line":0,"ch":169,"sticky":null},"value":"dateField_ksj7dsfe","invalid":false}],"isCmData":true}
Calculating length of service
YIDA in order to better optimize the content and quality of YIDA user manual, it takes you 3-5 minutes to fill in the document feedback questionnaire. The document feedback questionnaire is submitted anonymously, and the questionnaire information is only used for YIDA document experience feedback collection. Thank you for your support for YIDA!
-------------------- Get the latest information YIDA, welcome to follow US--------------------
