Lost with the Time in Excel and require a helping hand...

[RogueSchnitzel]

Hi Folks,

I'm lost trying to figure out how to aggregate the Time data in the table below to make it easier to work with and more presentable. Unfortunately, I don't have control over how the data is exported and I'm stuck to work with what I got.

Luckily I've found this Forum and I've been searching it for an answer as well as trying to piece together a solution for my dilemma, but no success thus far. Now I'm here, posting and hoping someone can pull me out of this void. ray:

My Challenge:

The times in table below are reflected as "hh:mm", e.g. E2 = 181:12 (181 hours and 12 minutes)

I would like it to reflect something to this effect "dd:hh:mm" or Decimal, but am open to other suggestions as well.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 79"]Agents
[/TD]
[TD="width: 64"]Tardy[/TD]
[TD="width: 64"]UPTO[/TD]
[TD="width: 70"]NCNS[/TD]
[TD="width: 64"]Total[/TD]
[/TR]
[TR]
[TD]Agent 1[/TD]
[TD]05:12[/TD]
[TD]176:00[/TD]
[TD]-[/TD]
[TD]181:12[/TD]
[/TR]
[TR]
[TD]Agent 2[/TD]
[TD]10:05[/TD]
[TD]114:15[/TD]
[TD]-[/TD]
[TD]124:20[/TD]
[/TR]
[TR]
[TD]Agent 3[/TD]
[TD]24:14[/TD]
[TD]68:45[/TD]
[TD]09:00[/TD]
[TD]101:59[/TD]
[/TR]
[TR]
[TD]Agent 4[/TD]
[TD]11:59[/TD]
[TD]72:00[/TD]
[TD]03:58[/TD]
[TD]87:57[/TD]
[/TR]
[TR]
[TD]Agent 5[/TD]
[TD]03:13[/TD]
[TD]48:00[/TD]
[TD]-[/TD]
[TD]51:13[/TD]
[/TR]
</tbody>[/TABLE]

This might be something easy, but I can't figure it out for the life of me.
Any help is greatly appreciated and would make my day!!

 

[sandy666]

like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Agents[/td][td]Tardy[/td][td]UPTO[/td][td]NCNS[/td][td]Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Agent 1[/td][td]

0.05:12:00​

[/td][td]

7.08:00:00​

[/td][td]-[/td][td]

7.13:12:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Agent 2[/td][td]

0.10:05:00​

[/td][td]

4.18:15:00​

[/td][td]-[/td][td]

5.04:20:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Agent 3[/td][td]

1.00:14:00​

[/td][td]

2.20:45:00​

[/td][td]

0.09:00:00​

[/td][td]

4.05:59:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Agent 4[/td][td]

0.11:59:00​

[/td][td]

3.00:00:00​

[/td][td]

0.03:58:00​

[/td][td]

3.15:57:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Agent 5[/td][td]

0.03:13:00​

[/td][td]

2.00:00:00​

[/td][td]-[/td][td]

2.03:13:00​

[/td][/tr]
[/table]

try custom format: d.hh:mm:ss

 

[RogueSchnitzel]

Hi sandy666 - Thank you much for your prompt reply!! Yes, that would definitely work for me.

However, when I select all the applicable Cells with the Time and go to " Number Format > Custom > d.hh:mm:ss " the data remains the same and doesn't change to how you have it. Do I need a specific formula in addition to that or am I missing something?

 

[sandy666]

I did this and it works:

as I can see in your post your time there is as text (aligned to the left) but it should be as number with time format (aligned to the right automatically)

try select each column then use Data - Text to Columns - Finish

 

[RogueSchnitzel]

Hi sandy666 - Thank you much for your prompt reply!! Yes, that would definitely work for me.

However, when I select all the applicable Cells with the Time and go to " Number Format > Custom > d.hh:mm:ss " the data remains the same and doesn't change to how you have it. Do I need a specific formula in addition to that or am I missing something?

I was able to figure it out!!! ray:

I left the formatting as you suggested and just added :00 to the end of every entry and tahh daahhh... 181:12 now shows as 7.13:12:00

Thank you @sandy666 !!

 

[sandy666]

GREAT !!!

Have a nice day