Need Excel help in converting figures...

[Anakha56]

Morning All,

As the header says I need some help in converting one set of figures into another type that is usable. I do realize that I will get laughed at because a lot of people will probably find this easy but anyway...

What I am doing is compiling my helpdesk tickets time into a format that can be used with my graph. Below is a example of my problems:

Ticket 1 I worked 5 minutes on so the figure is captured as 00:05 easy and usable. Ticket 2 was 10 minutes so it is captured as 00:10 again easy and usable. Now lets say ticket 12 was 1 week, how do I write that to match the other figures? 1 week is equal to 10 080 minutes, if I enter that figure Excel throws this up: 25920:00:00 and it throws everything off. So how should I write it to something I can use? I also have items that are in days eg 2 days and 35 minutes which also once I bring it down to hours and minutes throws everything off.

Seriously confused...

[Tribble]

Can't you use 00:00:00?
1st set for days (7 would be a week): 2nd set for hours : 3rd set for minutes.)

[Anakha56]

I shall investigate much later on. Massive problem has been picked up in our parts data which means huge amounts of work on my end and my boss is on leave FML...

/He will have to come in because he created the mess...

[Anakha56]

For some reason that does not work Tribs. If I set the format of the cell to "dd:hh:mm" it does not accept something like 10 minutes which I type out as "00:00:10", comes out "00:00:00". If I take one of the days say "02:00:45" it comes out "00:02:00" and the week one from "05:00:00" comes out as "00:05:00". Sense it make?

[doo_much]

The format 00:00:00 -> hh:mm:ss. So you're typing in 10 seconds?
Are you sure you want days and minutes on the same graph?

[Anakha56]

What I want to show on the graph is how many tickets we get per category and another column next to it how much time was spent in that category. Make sense?

[doo_much]

Not being sarcastic here - but draw a picture maybe?

[Anakha56]

For what I am trying to accomplish? Attached is a screenshot of my tickets (blue line) and there should be a red line next to it to indicate time spent.



/Yes I know the names are a bit off but hey...

[rustypup]

break time into seconds... work back...

Months are a special case.

Weeks/days/hours/minutes are doable...

mod() can be used to assign token values under the respective columns...

this question seems really familiar....

[Anakha56]

Have I asked this before? Sorry my memory is full of holes...

You will have to give me a wee bit more detail about what you are trying to say. My Excel skills hover between basic and intermediate...

[rustypup]

i'm not sure you asked... someone else certainly has...

1) convert your time data into seconds...
2) use static buckets to break this into usable formats ...
3) use MOD() to trim out the remainder, (mod() is synonymous with the modulus, (%), operator)

• minuteStatic==60
  hourStatic==(minute*60)==3600
  dayStatic==(hour*24)==86400
  weekStatic==(day*7)==604800

so, you have a time value of 2006887, (time)

Working backwards

• Weeks==round(time/weekStatic,0)==3
  Days==round(mod(time,weekStatic)/dayStatic,0)==2
  Hours==round(mod(mod(time,weekStatic),dayStatic)/hourStatic,0)==5
  Minutes=round(mod(mod(mod(time,weekStatic),dayStatic),hourStatic)/minuteStatic,0)==28

and so on... giving you:
3 weeks, 2 days, 5 hours, 28 minutes and 7 seconds

[Anakha56]

Wow... Okay will work on that at home where its quieter. Thank you

[Tribble]

There is an easier way - create a custom format. That is what I meant by my post. I did not mean to say use hh:mm:ss. You make your own, much easier than the puppies but do it his way and if you get stuck I can send you an example of what I mean.