Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
dd:hh:mm:ss
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
dd:hh:mm:ss
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
dd:hh:mm:ss
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|