Performing operations and calculations involving dates requires that one acquires familiarity with some of Excel’s Date and Time Functions.
The DATEDIF worksheet Function in Excel calculates the difference between two dates that are given as input. One can specify if you want the difference returned in days, months, or years based on the interval you specify within the function.
The syntax of the DATEDIF Function is:
where start_date is the initial date of interest, end_date is the end date or later date of interest. If the end_date is earlier than the start_date you will get the #NUM error returned, so be sure to enter your dates correctly. Interval can either be specified as “d” for days and then the days between the two dates are calculated, or “m” for months in which case the months between the two dates are calculated or “y” for years in which case the years between the two dates are calculated.
In the case of “m” specified this means the number of complete months between the two dates, so let’s say there is a difference of 2 months and 27 days between two dates – the DATEDIF function will return 2 months, if there is a difference of 2 months and 10 days between two dates, then if one uses “m” as the interval, 2 months will still be returned. When you specify “y” the same logic applies and the complete number of years between two dates is calculated.
Additionally, you can specify “md” as the interval in which case the function calculates the difference in days between the two dates without including the months or the years in the calculation, one can also specify “yd” as the interval in which case the difference is calculated in days without including the years in the calculation, or one can specify “ym” in this case the months between the two dates are specified without including the days and years in the calculation,
So, let’s get started with a simple example to illustrate how to use the DATEDIF Function.
Anaerobic digestion refers to a biological process, whereby a substrate is digested and the decomposition yields methane (CH4) and carbon dioxide (CO2), this entire process takes place in the absence of oxygen. The biomethane produced as a result of this process is considered to be a renewable energy source and can be used for electricity generation.
Anaerobic digestion is divided into four main stages namely hydrolysis of the substrate, fermentation, acetogenesis, and methanogenesis. Anaerobic digestion takes place in a bioreactor when the production of biofuel outputs is being considered.
Different micro-organisms in the bioreactor are responsible for the different stages making up the degradation process. Hydrolysis refers to the breaking down of complex molecules that make up the substrate. Fermentation of the degradation products yielded by the hydrolysis stage results in the production of alcohols, carboxylic acids, hydrogen, and carbon dioxide. The acetogenesis stage uses hydrogen and carbon dioxide to produce acetate. The final step is methanogenesis which involves the production of methane gas.
The micro-organisms responsible for the entire process, are usually obtained from an inoculating sludge, this can be an inoculating wastewater sludge obtained from a wastewater treatment plant, fish wastewater sludge obtained from an aquaculture farm, pig sludge obtained from a farm, or solid municipal waste sludge obtained from a municipal waste facility.
In our hypothetical example, a biochemist is evaluating the anaerobic digestion of green seaweed (the substrate) using four different sources of sludge separately, and the same bioreactor type (with the same settings) for each experimental run. He wants to see how long each inoculating sludge takes, to produce the optimum cumulative methane yield of 500 mL CH4/g VS. He wants to show the results for each sludge in days first and then months. He can, therefore, use the DATEDIF Function to deliver the results he wants. The source data is shown below.
Calculating the difference in days between the two dates
1) In order to calculate the difference in days between the start date and the end date for experimental run #1001 (wastewater sludge), i.e the time it took to reach the cumulative optimum biomethane yield, in cell F5 enter the following formula:
2) Upon pressing CTRL-ENTER a value of 65 is delivered which means that there are 65 days between the two dates specified. In the context of the experiment, this means it took the bioreactor containing the wastewater sludge 65 days to reach the cumulative optimum biomethane yield.
3) We can slightly adjust this formula to make the time period slightly more descriptive within the actual cell. In order to do this we delete the original formula, and enter the following formula which uses the concatenation operator to add the text days, in cell F5:
=DATEDIF(D5,E5,"d") &" days"
4) Upon pressing CTRL-ENTER the value 65 days is now returned.
5) Since we used relative references we can drag the formula down the column.
6) Taking the results into context, we can now see that the pig sludge was the most efficient sludge when used in conjunction with the green seaweed substrate, since it produced the optimum cumulative biomethane yield, in the shortest number of days, as compared to the other inoculating sludge used.
Calculating the difference in months between the two dates
1) In order to calculate the difference in months between the start date and the end date for experimental run #1001 (wastewater sludge), i.e the time it took to reach the cumulative optimum biomethane yield, in cell G5 enter the following formula:
2) Upon pressing CTRL-ENTER a value of 2 is delivered which means that there are 2 months between the two dates specified. In the context of the experiment, this means it took the bioreactor containing wastewater sludge, 2 months to reach the cumulative optimum biomethane yield.
3) We can slightly adjust this formula to make the time period slightly more descriptive within the actual cell. In order to do this we delete the original formula, and enter the following formula which uses the concatenation operator to add the text months, in cell G5:
=DATEDIF(D5,E5,"m") &" months"
4) Upon pressing CTRL-ENTER the value 2 months is now returned.
5) Since we used relative references we can drag the formula down the column, in order to see the time difference expressed in months.
6) The months’ data, of course, confirms what we saw with the days’ data with respect to the shortest interval between the two dates specified. We could, however, have a situation where the number of months between the two dates is the same as in the above example since the DATEDIF function returns complete months between two dates if “m” is specified. Experimental run #1001 and #1003 are shown as taking the same number of months since experimental run #1003 falls just short of 3 months and thus 2 months is delivered, and then we could then specify the days’ interval with the DATEDIF function in order to see the shortest time period truly reflected.
And there you have it.
Download Working File
In order to perform calculations involving dates, one will frequently need to utilize different date functions. The DATEDIF function calculates the interval between two dates, and one can have this interval delivered in days, months, years, or just days (excluding months and years), just days (excluding years), and just months (excluding days and years).
Review Section: Test your Understanding
1) What is the DATEDIF Function used to calculate?
2) What is the syntax of the DATEDIF Function?
3) What are the different intervals that the DATEDIF Function can return?
4) Use the DATEDIF Function to calculate the age of an individual born on 18 June 1980, and today’s date (the current date) in years, months, and then days.
- Calculate The Number of Days/Weeks/Months/Years between Two Dates in Excel
- Most Useful & Advanced Excel Functions List
- How to Use Excel EDATE Function
- Use Excel ROUND, ROUNDUP, ROUNDDOWN, MROUND & CEILING Functions
- How to Use DATEVALUE Function in Excel (6 Suitable Examples)
- Use DAY Function in Excel (With 3 Examples)