To compute the fraction, we will divide 1825 seconds by 86,400 seconds (i.e. The total seconds, therefore, are 1825 seconds. 30 minutes * 60 seconds/minute = 1800 seconds.Since our time is mentioned down to the seconds, we will compute 30 minutes and 25 seconds in terms of total seconds, like so: This leaves out the 30 minutes and 25 seconds, which still need to be factored into the output. Excel will first compute the difference in terms of days, and return 1 day (i.e. The total difference between both times is 24 hours, 30 minutes, 25 seconds. Let’s work out these numbers to see what is cooking inside the formula. Here is how Excel computes this return: (Number of days between both dates + a fraction for the difference in the number of hours). = INT((A2 – B2) * 24) & " hours" //where A2 is EndDate and B2 is the StartDate Subtracting 2 cells containing date and time is a straightforward process, but the formula looks moderately long because of the mechanics involved for a polished outcome. For example, 0.25 is 6 am and 0.5 is noon (or half a day). Just like it assigns a serial number for a date, it assigns a fraction for time. Get Difference Between Two Dates (with Time) in Hoursīefore we get into these computations, it is important to know how Excel interprets time. When you add a concatenation operator between these formulas, all the returned strings are displayed in a single cell giving us our final output "9 years, 5 months, 21 days". The second formula computes months and adds the string " months," to give us the output "5 months," and the third formula returns "21 days". = DATEDIF(B2,A2," y")&" years," & DATEDIF(B2,A2," ym")&" months," & DATEDIF(B2,A2," md")&" days"Īdding a concatenation operator allows us to display the output from these 3 formulas in a single cell.įirst, the formula computes years and adds the string " years," which gives us the output "9 years" in our example.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |