3.12 Time Calculations
In short: Time is a fraction of a day: 1 hour = 1/24, 1 minute = 1/1440.
Time is a fraction of a day: 1 hour = 1/24, 1 minute = 1/1440.
| Need | Formula | Example result |
|---|---|---|
| Delivery duration | =D2-C2 (Delivered time − Order time) formatted h:mm |
19:42 → 19:51 = 0:09 |
| Duration in minutes | =(D2-C2)*1440 |
9 |
| Build a time | =TIME(19,42,0) |
19:42 |
| Hour of order | =HOUR(C2) |
19 |
| Crosses midnight | =MOD(D2-C2,1) |
23:55 → 00:07 = 0:12 |
| Total hours > 24 | format [h]:mm |
30:15 |
| Rider pay at ₹90/hour | =E2*24*90 (E2 = hours worked as time) |
Worked example – rider shift. Raja logs in at 17:30 and out at 23:45 at the Hadapsar store. =MOD(B2-A2,1) → 6:15. Hours as a number: =MOD(B2-A2,1)*24 → 6.25. Pay: =6.25*90 → ₹562.50.
Ravindra Bagale's Tip
For a shift that ends after midnight, =End-Start becomes negative and shows ##### – many students panic. Use MOD(End−Start,1). And if you want to multiply hours by a rate, first multiply by 24 – a time value is a fraction of a day, not hours.
Ravindra Bagale's Tip – मराठी
रात्री 12 नंतर संपणाऱ्या shift ला =End-Start negative येतो आणि ##### दिसतं – बरेच students घाबरतात. MOD(End−Start,1) वापरा. आणि hours ला rate ने गुणायचं असेल तर आधी ×24 करा – time value हा दिवसाचा fraction असतो, hours नाही.
Ravindra Bagale's Tip – हिंदी
रात 12 के बाद खत्म होने वाली shift में =End-Start negative आता है और ##### दिखता है – बहुत से students घबरा जाते हैं. MOD(End−Start,1) इस्तेमाल करो. और hours को rate से गुणा करना हो तो पहले ×24 करो – time value दिन का fraction होती है, hours नहीं.
Practice task
From order time and delivered time, compute delivery minutes. Classify orders by hour: Morning (6–11), Afternoon (12–16), Evening (17–21), Night.