Time and Time Formatting

[santeria]

Currently, I have a Data Extraction that has a Slight (?? ) problem with time, and time Formatting.
The Relevant Data Lines Affect 2 Columns, but only because the Second Column has a SUM formula.

All other Summaries work great, however G2 on the First Summary sheet of the file persists in presenting time in Seconds, so for the 15th of October , the Average Handle time is 637 seconds, and the same occurs for G6 where the summary is 629 seconds; I can't format these cells using any standard selected procedure.
When Transferred to the Second Monthly Summary file, 637 Seconds, set as a Number, becomes 14:48 in time, and the other line becomes 0:00 when set to time format, but reverts to 629 when Changed to Number format.

The Summary Sheet for the Bosses, a Third file, Presents everything else perfectly except that the 14:48 is inherited in the AHT column in the "Skill 77" Tab, and the time formatted AHT column on tab "17 Skill" goes to 0:00 and so the inherited time as seconds, which refuses to be formatted to Minutes and seconds transfers over.

Is there a formula I need to Apply to change the Seconds to Minutes, or something which will Correct the Data Transfer problem.

Ta

 

[Chitosunday]

time is excel is equated into fraction based on a day. Hence, if you want to
convert your data into seconds format is then input your 637 seconds as
637/60/60/24. By dividing 60, you convert it to minutes, then 60 again to convert it to hours , then 24 to convert it to a day.

If you want to convert that into minutes the function is minute(ur range) and format it as number.

 

[santeria]

Thanks.
With the result of the the equation, what is the best way to present the result in a time format, but without the AM or PM designation? Or, can the AM /PM tag be removed if the format is in time ?

Ta



*******************

637/60/60/24. By dividing 60, you convert it to minutes, then 60 again to convert it to hours , then 24 to convert it to a day.

If you want to convert that into minutes the function is minute(ur range) and format it as number.

 

[santeria]

So Far, the Formula produces a Minute value, works great.
The Odd thing is that a Cell calculating hold time is not working.
=SUM(G2-H2-I2) is the Formula, with G2 being the AHT formula I had trouble formatting,and row H is ACD time, and Row I is ACW time.

I am not sure what is failing.

Ta.

 

[Chitosunday]

Can you use colo's html maker utility so i can disect the problem. Not to clear about your problem. What is the value of g2, h2, i2 now. Might reconsider using fraction of a day to
make sure your formula works.

 

[santeria]

So Far the Value's are( I hope this Displays okay, The "/" are column separators.... If I understand the HTML utility, I need to load it on to the PC, and this is a work PC that needs Admin rights to load anything):

AHT / Avg Acd Time / Avg ACW Time / Average Hold Time
10:37 / 6:04 / 3:07 / =SUM(G2-H2-I2)



10:29 / 6:25 / 2:28 / = SUM(G6-H6-I6)


At this Point the Formula ( Insisted by a Manager ), calculates 45:23 for the First line and 17:24 for the Second line. The Action is supposed to be G minus H minus I, which gives the Average Hold time... Minor, but important for the decision makers.

Ta.

Can you use colo's html maker utility so i can disect the problem. Not to clear about your problem. What is the value of g2, h2, i2 now. Might reconsider using fraction of a day to
make sure your formula works.

 

[santeria]

The Formula xxx/60/60/24 works to change the original second value:
Oddly enough it confirms my original figures.

Anyway, the issue at this point is a formula in the third column, whereby the values have to be subtracted.... However since the formatting is other than what is required for time values( it just appears as time values at the behest of management) I have to either try and reformat the fields and retain the values, or do the calculations elsewhere.

Ah well.... At least today is payday


Ta

 

[santeria]

If Anyone can format time without AM or PM coming up in the formula bar, I'd appreciate a tip.
I've looked through just about everything I can find.
Or just a way to remove the AM/PM in the Formula bar display for the file.


Ta

 

[santeria]

If Anyone can format time without AM or PM coming up in the formula bar, I'd appreciate a tip.
A key part of this is allowing the format to display minutes and seconds, or for the entry to allow, for example, input of 6:05, and the default being to 6 minutes and 5 seconds, not 6 hours and five minutes, and especially not with the AM or PM tag.

I've looked through just about everything I can find.
Or just a way to remove the AM/PM in the Formula bar display for the file.


Ta

 

[Chitosunday]

Sorry, tried also everything but you cannot enter minute and second only . It always convert your data to hours & minutes & seconds.

I could not also understand your formula because the first formula should give me 26 seconds
(10:37-6.04-3.07) and not 45 minutes and 23 seconds.