Another excel question

foshank

I have been trying to add and display duration time in excel. I want to be able to add and display total time in minutes and seconds only. I have been formatting the cells under custom using [h]:ss. But when I enter data, the resulting figure is wrong. Am I entering the data incorrectly or am I missing a step?

Neil_Jones

So what result are you entering and what result are you expecting?

=MOD((E14-D14),1)*24 will show you the difference between the end time (in E14),  and the start time in D14.

(remember to apply appropriate formatting) so if you enter 9:30 in D14 and 17:00 in E14, the formula should show you 7.5.  It should work (in theory) for any denomination assuming you set the filter up correctly.

foshank

Neil_Jones said:

So what result are you entering and what result are you expecting?

=MOD((E14-D14),1)*24 will show you the difference between the end time (in E14),  and the start time in D14.

(remember to apply appropriate formatting) so if you enter 10:00 in D14 and 17:00 in E14, the formula should show you 7.

My wife has to undertake a set of different exercises every day and has to record the time elapsed in minutes and seconds. I wish to enter say for example 35mins and 20 secs today. Tomorrow may be 33 min and 49 sec. I wish to add the total time for a month and then display the average daily time in mins and secs. Is that possible in excel?

double_dutchy

You say you used custom format [h]:ss, surely you want [m]:ss for minutes and seconds?

Formatting cells in a particular way for display purposes doesn't always mean the input is interpreted how you would like it to be. Excel will interpret input of 33:22 as 33 hours and 22 minutes, even if the cells are custom formatted [m]:ss, you need to input as 0:33:22 to get the correct result.

If you have durations greater than one hour, e.g. 70 minutes and 5 seconds you can enter either as 0:70:05 or as 1:10:05, with cell formatted as [m]:ss both will display as 70:05

You can sum and average these values just as you would any other numbers in excel - just make sure the result cells are also formatted [m]:ss

foshank

Many thanks @double_dutchy. I did have [m]:ss on my spreadsheet; my original post contained a typo. As there is a separate [h]:mm:ss option I did not realise that I still needed to include the extra 0: to account for the missing hour. At least everything is now up and running smoothly