Time format DD is restarting after 31 days back to day 1

[tarek]

Hi,

I am trying to sum data in a column which is formatted as [HH]:MM; as the number of hours are in thousands now I thought it'll be helpful to give the user the opportunity to see data in days to better quantify the data point. To do so I convert the data from [HH]:MM to DD:HH however now when the hours are equivalent to more than 31 days, the cell is restarting at 1.
Example:

[HH]:MM = 2207:00
DD:HH = 31:23

[HH]:MM = 2208:00
DD:HH = 01:00 (instead of moving to 32)

How can I prevent the DD:HH from restarting?

Thanks for all your help on this

 

[Scott Huish]

You can't use the day format to do that because it is used to specify days of the month which only go up to 31.

But how do you get that 2207 hours is 31 days, 23 hours? It's closer to 92 days.

 

[tarek]

You can't use the day format to do that because it is used to specify days of the month which only go up to 31.

But how do you get that 2207 hours is 31 days, 23 hours? It's closer to 92 days.

Hi Scott, I am using Excel 2010. let's say you have cell A1 formatted as [HH]:MM and Cell B1 formatted as dd:hh
now in cell A1 enter 2207:00 and in B1 enter =A1
A1 now will show 31:23

 

[Andrew Poulsom]

Surely 2207:00 is 91 days (plus a bit) rather than 31 days. You can use the formula:

=INT(A1)&":"&ROUND(MOD(A1,1)*24,0)

 

[tarek]

Surely 2207:00 is 91 days (plus a bit) rather than 31 days. You can use the formula:

=INT(A1)&":"&ROUND(MOD(A1,1)*24,0)

Many thanks Andrew, your solution worked like a charm! I appreciate your help on this.

Tarek

 

[tarek]

Andrew, the issue am having now is that excel is treating results from =INT(A1)&":"&ROUND(MOD(A1,1)*24,0) as text and I am not able to graph results. any suggestions how to resolve this?

Thank you

 

[Andrew Poulsom]

Wh don't you chart the original data? Or to convert the result of the formula to a value:

=(INT(A1)&":"&ROUND(MOD(A1,1)*24,0))+0

 

[tarek]

Hi Andrew, currently the information is being entered in decimal example 1.75 hours is really 1:45; therefore, I created a column translating decimal hours to more user friendly format by dividing decimal hours by 24 and formatting the column in [HH]:MM . when creating the dashboard several views are depended on aggregate view of total hours. My graphs are PivotCharts where the hours is based on the additional column I created. I thought translating hours to days would make the graph more effective and this is how the issue came up.

I do appreciate you taking the time to help.. Thank you Sir.

Tarek