All we need is an easy explanation of the problem, so here it is.
I have time values in two cells in an Excel sheet and I need the difference of
the times in minutes.
The cells in the Excel are like below:
A B C 1 6:38 8:23 1:45:00
A is the start time
B is the end time
C is the time difference (B1-A1) which is displayed in hh:mm:ss format
A and B cols are formatted as custom hh:mm and cell C is formatted as custom hh:mm:ss.
But I need to get in the value in cell C1 as 105 instead of 1:45:00.
Could you please help in displaying time only in minutes and giving the right format?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Just set the cell to be shown in numerical format and it will show a decimal number.
The integer part corresponds to the date value (i.e. the day) and the decimal part to the time of the day.
If you multiply the numerical value of 1:45 (which is 0,07) by 24×60 (the number of hours in a day and the number of minutes in an hour) you will obtain the value in minutes of your cell (105).
Just format your cell as
The square brackets around the
mm tell Excel to show the total rather than what would be shown if you took the total hours off. The brackets also force Excel to interpret
mm as minutes, not as the month. (Without the brackets,
mm refer to the month if not preceded by
hh for hours or followed by
ss for seconds.)
1:45:00 with format
mm:ss would ignore the hours and show
[mm]:ss will show as
105:00, and both
[mm] would show
mm would show
12, being the month of Excel’s default date, which is December 31, 1899).
This also works for hours and seconds. Like
h:mm would show
3:10, but both
27:10, while both
s would show
I think this will work:
- A1 6:23
- B1 8:23
- C1 =(B1-A1)*24*60
Just make sure you format C1 as “General”
Just format your C cell as
As some people have already said,
If you multiply the numerical value of 1:45 (which is 0,07) by 24×60=1440 (the number of hours in a day and the number of minutes in an hour) you will obtain the value in minutes of your cell (105).
Short version: C1 =(B1-A1)*1440
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂