3.12 Time Calculations
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
Raatri 12 nantar sampnarya shift la =End-Start negative yeto aani ##### disto – khup students ghabartat. MOD(End−Start,1) vapra. Ani hours la rate ne gunaycha asel tar aadhi ×24 kara – time value ha divsacha fraction aahe, hours nahi.
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.