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) |
About this data: Teaching sample for a fictional shop “Summit Schools”. Figures and names (e.g. Rohan Patil) are made up for this lesson on time calculations.
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.
Loading practice tasks...
- Delivery minutes (Easy): Find the minutes between B2 and C2.
- Hour of the order (Easy): Get the hour from B2.
- Calls: login minute (Easy): Get the minutes from Calls!B4.
- Calls: day shift hours (Easy): Find Ravina's hours from Calls!B2 to Calls!C2.
- Make a time (Medium): Make the time 19:42 with TIME.
- Past midnight (Medium): Find the minutes from B3 to C3.
- Calls: night shift hours (Medium): Find Salman's hours from Calls!B3 to Calls!C3.
- Shift hours (Hard): Find Raja's shift hours from B4 to C4.
- Shift pay (Hard): Find Raja's pay: shift hours times B5.
- Calls: shift pay (Hard): Find Amir's pay: hours from Calls!B4 to Calls!C4 times Calls!B5.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
