![]() Once we have the basic LET version working, we can easily extend the formula to do more complex processing, without redundantly running the same calculations over again. With the LET function, the last argument is the calculation or value that returns a final result. Notice we have assigned results from DATEDIF to the variables years, months, and days, and these values are concatenated together in the last line inside the LET function. Years&" years,"&months &" months," &days &" days" Here is the formula updated to include new variables for years, months, and days: =LET( If we assign these values to named variables, we can more easily combine them later in different ways, which becomes more useful in the extended version of the formula explained below. The next three opportunities for variables are the results from DATEDIF for years, months, and days. This makes it easier to read the formula and helps reduce errors. The start and end dates are then reused throughout the formula. The result from this formula is the same as the original formula above, but the reference to B5 and C5 occurs only once. Notice all instances of B5 and C5 in DATEDIF have been replaced by start and end. ![]() Once we assign values to start and end, we only need to refer to cell C5 and D5 one time. As a first step, we can add line breaks and then define variables for start and end like this: =LET( The first two are the start date and the end date, which are reused throughout the formula. Looking at the formula, there are at least five opportunities to declare variables. The main purpose of variables is to define a useful name that can be reused elsewhere in the formula code. The LET function (new in Excel 365) can simplify some formulas by making it possible to define and reuse variables. In order to use the LET function on this formula, we need to think about variables. DATEDIF(B5,C5,"y") // yearsīecause we want to create a string that appends the units to each number, we concatenate the number returned by DATEDIF to the unit name with the ampersand (&) operator like this: DATEDIF(B5,C5,"y")&" years" // years stringĭATEDIF(B5,C5,"ym")&" months" // months stringĭATEDIF(B5,C5,"md")&" days" // days stringįinally, we need to extend the idea above to include spaces and commas and join everything together in one string: =DATEDIF(B5,C5,"y")&" years, "&DATEDIF(B5,C5,"ym")&" months, " &DATEDIF(B5,C5,"md")&" days" To get whole years, whole months, and days between the dates, we use DATEDIF like this, altering only the unit argument. ![]() In the example shown, we calculate years, months, and days separately, then "glue" the results together with concatenation. See this page on the DATEDIF function for a full list of available units. ![]() time in months, time in months ignoring days and years, etc.) and these are set by the "unit" argument in the function. There are several variations available (e.g. The DATEDIF function is designed to calculate the difference between dates in years, months, and days. For example, given a start date of and end date of, the result should be a string like this: "1 years, 6 months, 0 days" In this example, the goal is to output the time between a start date and an end date as a text string that lists years, months, and days separately.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |