Even though the dates are just one day apart, they lie in different months. MONTHS("" "" 1) returns 14, the number of months between April 2010 and June 2011. Even though both dates are the last day of their months, the last month is not counted, because the day is less than 31. 31 March to 30 April is not considered a whole month, even though they are both the last day of their months.
MONTHS("" "" 0) returns 14, the number of months between 3 April 2010 and 17 June 2011. I know it would be no problem to perform this calculation on the spreadsheet using minutes and decimals but minutes and seconds is more easily related to by people. If startdate is after enddate the result will be negative. I am currently using a calculator to convert the times in minutes and seconds to minutes and decimals, in order to work out average speeds(mph). In other words it returns MONTH(enddate) - MONTH(startdate) + 12 * (YEAR(enddate) - YEAR(startdate)). Heres an equation for hours, minutes, and seconds: ((HOUR(D1033)+(MINUTE(D1033)/60)+(SECOND(D1033)/3600))-(HOUR(C1033)+(MINUTE(C1033)/60)+SECOND(C1033)/3600))60 There are two things to be aware of regarding this equation: 1. For instance, 12:30:11 and 18:42:12, get the hour difference is 6, minute difference is 12, second difference is 1.
And I wrote a simple DAX calculation which will give you the result. However, sometimes you need this calculation to be dynamic as a measure in DAX. But sometimes, you may want to calculate the time difference in one time unit ignoring others. In Power Query, there is an easy way to use Duration and get the number of days, hours, minutes and seconds from it. If mode is 1, MONTHS identifies the month that startdate and enddate each lie in, and returns the difference between those months. Normally in Excel, you can use the simple formula EndTime-StartTime,to calculate the difference between two times. This will help others to find the correct solution easily. Please click Mark as Best Response if my post helped you to solve your issue. MONTHS(startdate enddate mode) if mode is 0, MONTHS returns the number of whole months between startdate and enddate, day of the month to day of the month (see examples). General format for your formula should something be like: TEXT (DATE ( Column2)-DATE ( Column1),'h:mm:ss') Reference : Date and time formulas. a number of reports, including: Renewals: the renewal rate is calculated by. This function is only available if the Analysis AddIn is installed. Resolution Analysis: Displays the average and median time in minutes it. Show Printable Version Subscribe to this Thread Rate This Thread. LinkBack URL About LinkBacks Thread Tools. Returns the number of months between two dates. Microsoft Office Application Help - Excel Help forum Excel Formulas & Functions Calculate with hours and minutes Results 1 to 8 of 8 Calculate with hours and minutes.