Let it be any calendar or clock, it has some base from which counting of days or seconds is started. Excel works on the same concept. Every date or time, is represented as number with reference to some base.
We entered date as 5th Feb 2019 and time as 17:00:00 i.e 5.30 pm (as shown in above screen-clipping) and applied Custom cell formatting of dd-mm-yyyy hh:mm:ss. Date and Time is separated by space in Excel. Time needs to be entered in 24 hours format. We applied number format to that cell, and date/time got converted to some number. Every date or time or both is internally stored as number by excel. If that number's formatting is changed to Date or Time format, it gets formatted respectively.Let's understand logic of that number.
Integer portion of number represents Date
Decimal portion represents Time.
Every date must have some base from which to start counting. Excel treats 01-01-1900 i.e. 1st Jan 1900 as 1. This counting of days elapsed goes as shown in below table.
| Date | Integer |
|---|---|
| 01-01-1900 | 1 |
| 02-01-1900 | 2 |
| 31-01-1900 | 31 |
| 01-02-1900 | 32 |
So, applying this logic 05-02-2019 value comes to 43501
Using the converter below you can know Integer for respective Date calculated by Excel
| Date | |
| Integer | 1 |
Time is represented as fraction in Excel which can range between 0 (zero) to 1 (one). Each day is divided into 24 hours.So fraction for each hour is calculated as shown in below table
| Time | Fraction |
|---|---|
| 00:00:00 (12 am) | 0 ÷ 24 = 0 |
| 01:00:00 (1 am) | 1 ÷ 24 = 0.416666 |
| 02:00:00 (2 am) | 2 ÷ 24 = 0.833333 |
| 12:00:00 (12 pm) | 12 ÷ 24 = 0.5 |
| 13:00:00 (1 pm) | 13 ÷ 24 = 0.541666 |
There are 24 hrs × 60 mins = 1440 min in a day. So fraction for each minute will be 1 ÷ 1440 = 0.000694
So in our example fraction for time 17:30:00 was calculated as below
| Hours | 17 ÷ 24 | 0.708333 |
| Minutes | 30 ÷ 1440 | 0.020833 |
| Total | 0.729167 |
There are 24 hrs × 60 min × 60 sec = 86400 sec in a day. So calculation for seconds will be done accordingly.
Using the converter below, you can know Decimal for respective Time
| Date | |
| Fraction | 0 |
As illustrated in above screen-clipping, you can simply subtract bigger date from smaller date and get number of days between any two dates. Excel internally just subtracts two numbers 43501 and 43435, which are days difference between those dates.
Suppose we have biometric in-out time access data, and we want to find number of hours between In & Out time of specific worker. So we will subtract In Time (smaller value) from Out Time (bigger value).
We can see difference of 8:28 i.e 8 hrs 26 minutes in cell B3. Let's say, we want to calculate wages @ 400 per hour based on number of hours worked. So we multiple cell B3 by 400 to arrive at wages. Excel automatically converted this value (in cell B4) to time format, so we will change the formatting to number format.
Answer should in any case be greater than 8 × 400 = 3200. But we got some strange value of 141.11 in cell B4 after we changed the formatting of cell B4. So something is incorrect.
Let's inspect value in cell B3 where number of hours worked was calculated using subtraction. We will change formatting of this cell to number.
Cell B3 is having value of 0.35 instead of 8.xx (xx = fraction for 36 minutes). This techinique of subtration works perfectly for date, but not time. Let's understand why it happened
Both In & Out time are internally fractions. So excel just subtracted those 2 fractions and later changed it's format back to time formatting.
But the next question is how that number 0.3528 came ?
(8 ÷ 24) hrs + (28 ÷ 1440)
= 0.3333 + 0.0194
= ~0.3528
So, we need to convert time calculations to Hours or Minutes, if we want to apply further calculations. So, we have again multiply back by 24 to convert it into hours
0.3527 × 24 = 8.46 hrs
If we want answer in minutes then it would be
0.3527 × 1440 = 508 min (approx)
So, let's modify cell B3 as shown below
Now excel is displying correct answer