how to calculate time from decimal numbers

**arkantos** · 11-09-2011, 09:38 AM

Hello there!

got stuck on another Excel calculation step.

attached, is an excel file.
you will see that there are 4 columns.

the decimal numbers are time in 24 hour format
so 8.05 means 8:05 and 13.3 means 13:30

is there any way that the decimal time entries can be added up and sub-totaled for the day?

i am open to all suggestions, but the data set is as it is, i cant do anything to change that
12 employees with nearly 1 year of data, comes up to about 17,500 cells.

**pike** · 11-10-2011, 02:40 AM

Hi arkantos
probably better to convert the values to time and then add then in the correct format
http://www.exceltip.com/st/Convertin...Value/944.html

**zbor** · 11-10-2011, 02:55 AM

It would be much easier if you would enter times as times.

**arkantos** · 11-10-2011, 01:54 PM

Pike,
thank your for your inputs. unfortunately, the data is already there, as i mentioned, and changing 17500 and growing cells of data is just not my cup of tea.

Zbor,
my goodness, it IS possible, you are a godsend!
i seem to understand the logic of the equation, but can you please explain this part:

SUBSTITUTE(TEXT(F30,"00,00"),",",":")
why all the empty commas with quotes?

thank you both for replying!
Arkantos.