Hi Excellers,
I have some time fields Time 1- Time 4, as pictured below. Without changing the format of the cells, what would be the formula to covert Time 1 of 1:35 to 95 seconds, and Time 3 of 1:29 to 89 seconds?
Hi Steve,
The solution depends on the time format.
I think that Excel read the time as hour:minute as shown in the below screenshot:
If the time format that shown in the cell is hour:minute as the above screenshot, you need this formula:
=TEXT((HOUR(A1)*60)+MINUTE(A1),"0")
But, if the format is really minute:second, you need to use this formula instead:
=TEXT((MINUTE(A1)*60)+SECOND(A1),"0")
However, you have to make sure that you read the format as Excel read it.
You may think that the format is minute:second while Excel read it as hour:minute.
Hope that helps
Accepted Solutions
Hi Steve,
The solution depends on the time format.
I think that Excel read the time as hour:minute as shown in the below screenshot:
If the time format that shown in the cell is hour:minute as the above screenshot, you need this formula:
=TEXT((HOUR(A1)*60)+MINUTE(A1),"0")
But, if the format is really minute:second, you need to use this formula instead:
=TEXT((MINUTE(A1)*60)+SECOND(A1),"0")
However, you have to make sure that you read the format as Excel read it.
You may think that the format is minute:second while Excel read it as hour:minute.
Hope that helps