For whatever reason you may need at some point to convert a time to a number of minutes since the start of that day. Here is a tip on how to do that successfully.
There are two different ways to get to this result.
There are 60 minutes in an hour and 24 hours in a day. Therefore there are 60 * 24 minutes in a day which is =1440.
If we take a time of day we can calculate how many minutes there are from the beginning of the day to that particular time.
In a spreadsheet type the following:
I have chosen the time to be 8:25 in the morning.
In the cell B2 type the following formula: =A2*1440
The result will be.
Should you need seconds to be calculated in this then here is another way of doing this calculation.
Here we shall be using three functions: Hour, Minute and Second functions.
The hour function
- I have selected the cell A6.
- Type a time with an hour and minutes. i.e. 9:30
- In the cell to the right locate and use the hour function from the Insert function tool. ( I like to use the one found in the formula bar as it takes you to the wizard when you choose a function.)
- Select the cell with the time in it. ( A6)
- Press the enter key as this is the only thing you require at this time.
The answer is 9.
- In A8 type the same time again.
- In the cell to the right (B8) locate and use the minute function.
The answer is 30.
- In the cell A10 type the same time but with seconds too. i.e. 9:30:20
- In cell B10 locate and use the Second function.
The Answer is 20.
Combine all three time functions
We are going to combine (use more than one function together) these three functions. As we are looking for the answer in minutes we will need to multiply hours by 60 and divide seconds by 60.
- Select cell B12.
Type the following in the cell B12.
- Press the Enter key.
The result is 570.33
Here is the whole spreadsheet of workings. One image of the results and the other showing the formulae.
Hope this tip has helped a few of you out there!
Please be careful if you use the Insert function wizard as this is the result you will get 18. which is wrong! This is because the formula looks like this =HOUR(A10*60+MINUTE(A10+SECOND(A10/60))). The brackets are in the wrong places!!!! Remember BODMAS the arithmetic operations in Excel.