Hello,

I have a list of durations of time in days, hours, minutes and seconds.

I want to type these into a column and then calculate the total at the end.

The problem I find is that it doesn't seem to recognise the format
dd:hh:mm:ss when I type it in as 01:21:14:23 it thinks it's text even though
I have changed the cell format to custom dd:hh:mm:ss

The sum at the bottom works out the amount of days etc fine in that format
but only if I enter the durations in the rest of the column as hh:mm:ss

Could someone please help, it's driving me crazy!

Example of what I want to do:

01:07:48:03
07:11:53:35

=sum(A1:A2)

You either need to change the way you input the values, like:
1/1/1900 7:48:03

or, can keep your current input, and then use this helper column:
=--LEFT(A2,2)+TIMEVALUE(MID(A2,4,999))

And sum the helper column.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tuaktaka" wrote:

> Hello,
>
> I have a list of durations of time in days, hours, minutes and seconds.
>
> I want to type these into a column and then calculate the total at the end.
>
> The problem I find is that it doesn't seem to recognise the format
> dd:hh:mm:ss when I type it in as 01:21:14:23 it thinks it's text even though
> I have changed the cell format to custom dd:hh:mm:ss
>
> The sum at the bottom works out the amount of days etc fine in that format
> but only if I enter the durations in the rest of the column as hh:mm:ss
>
> Could someone please help, it's driving me crazy!
>
> Example of what I want to do:
>
> 01:07:48:03
> 07:11:53:35
>
> =sum(A1:A2)

Wow that's a lot of faffing about!

Thanks for your help though

"Luke M" wrote:

> You either need to change the way you input the values, like:
> 1/1/1900 7:48:03
>
> or, can keep your current input, and then use this helper column:
> =--LEFT(A2,2)+TIMEVALUE(MID(A2,4,999))
>
> And sum the helper column.
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Tuaktaka" wrote:
>
> > Hello,
> >
> > I have a list of durations of time in days, hours, minutes and seconds.
> >
> > I want to type these into a column and then calculate the total at the end.
> >
> > The problem I find is that it doesn't seem to recognise the format
> > dd:hh:mm:ss when I type it in as 01:21:14:23 it thinks it's text even though
> > I have changed the cell format to custom dd:hh:mm:ss
> >
> > The sum at the bottom works out the amount of days etc fine in that format
> > but only if I enter the durations in the rest of the column as hh:mm:ss
> >
> > Could someone please help, it's driving me crazy!
> >
> > Example of what I want to do:
> >
> > 01:07:48:03
> > 07:11:53:35
> >
> > =sum(A1:A2)