Part 5: “&DATEDIF(B2, C2,” MD”) Now we have a difference between Year and the Month. If the result of part 3 is 4, then this part will add Months to part 3, i.e. Part 4: &” Months” This is the addition to part 3. “YM” can give the difference between months. We find the difference between the months in this part of the formula. Part 3: &DATEDIF(B2, C2, “YM”) Now, we found the difference between years. For example, if the first part gives 4, the result will be 4 Years. Part 2: &” Year” is added to a previous part of the foSo, forla. Part 1: =DATEDIF (B2,C2, “Y”) this is the starting date and ending date, and “Y” means we need to know the difference between years. This can be useful to find the difference between year, month, and day. Total Tenure, we need to use the DATEDIF function.ĭATEDIF function is an undocumented formula where there is no IntelliSense list for it. If we just deduct the relieving date from the joining date, we get the only number of days they worked we get it in detail. DATE function alone cannot do the job for us. This establishes “14” as the day of the date in D2.Here we need to use the DATEDIF function to get the result as per our wish. The RIGHT function looks at cell C2 and takes the first 2 characters starting from the very right and moving left. Because the formatting of D2 set to Date, the “0” isn’t included in the final result. This establishes “03” as the month of the converted date in cell D2. It starts at the 5th character, and then takes 2 characters to the right. This establishes “2014” as the year of the converted date in cell D2. The LEFT function looks at cell C2 and takes the first 4 characters from the left. To convert it into a date, the DATE function was used in conjunction with the LEFT, MID, and RIGHT functions. For example, in the following illustration, cell C2 contains a date that is in the format: YYYYMMDD. If this is the case, you can use the DATE function to convert the information into dates. This is may be because the numbers don't resemble a typical date, or because the data is formatted as text. But sometimes the dates aren't recognizable. If you open a file that came from another program, Excel will try to recognize dates within the data. For example, DATE(2008,1,-15) returns the serial number representing December 16, 2007. If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. For example, DATE(2008,1,35) returns the serial number representing February 4, 2008. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. A positive or negative integer representing the day of the month from 1 to 31. For example, DATE(2008,-3,2) returns the serial number representing September 2, 2007.ĭay Required. If month is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the year specified. For example, DATE(2008,14,2) returns the serial number representing February 2, 2009. If month is greater than 12, month adds that number of months to the first month in the year specified. A positive or negative integer representing the month of the year from 1 to 12 (January to December). If year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value. If year is between 19 (inclusive), Excel uses that value as the year. If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, "07" could mean "1907" or "2007." Four digit years prevent confusion. Tip: Use four digits for the year argument to prevent unwanted results.
0 Comments
Leave a Reply. |