Time format that you can calculate

[PLayreau]

Hi all!

I'm looking for some help.

I can extract a worksheet with different time formats that I would like to be able to sum, substract...etc

For example here are the 2 different time formats appearing in the document:

1- 07:04:23 for 7 hours 4 mins 23 secs

2- 2.35h for 2 hours and 0.35 of an hour

What would be the best format to translate those to a value that I can calculate?


Many thanks

Pierrick

 

[Legacy 143009]

The first one of course.

 

[PLayreau]

The first one of course.

thanks for your answer,

Yes but how do I have to format it so I can add those times between themselves?

And how can I translate the other format to this one?

thanks

 

[Legacy 143009]

thanks for your answer,

Yes but how do I have to format it so I can add those times between themselves?

And how can I translate the other format to this one?

thanks

=TEXT(A1,"hh:mm:ss")

 

[FormR]

And how can I translate the other format to this one?

Hi and welcome to the forum!

Here is a formula option that converts that text string to an excel time serial number which can easily be used in calculations.

Excel 2013/2016
	A	B
1	2.35h	02:21:00
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1,"h","")/24