converting time to numbers

[draacor]

I need to convert a time into numbers so that i can take those numbers and put them on a weighted average. I couldnt figure out how to do this easily so i came up with the following formula:

=if(j2 = "12:50 AM",10,"")

i want 1:00 AM to be the average mean and if its 12:50 to 1:00 AM i want it to be positive incremental numbers, if its past 1:00 AM i want it to be negative numbers.

The above formula doesnt work and im not sure why, any suggestions?

 

[macropod]

Hi draacor,

You problem description is confusing! What is an 'average mean'? That's the same as saying you want the average average ... Do you perhaps want the mean, mode or median?

With the statement "i want 1:00 AM to be the average mean", it also seems like you're maybe looking for a goal-seeking function. But from/for what?

Perhaps you could re-phrase the problem.

Cheers

[MS MVP - Word]

 

[GSCaupling]

Hi draacor,

Time in Excel is a number. When we see 12:50 AM, for example, that is simply a format applied to the underlying number. The integer of the date number can tell us the month, day and year, while the fractional part tells us hours, minutes and seconds.

It appears that you might be tracking how early or late an employee is, but that is just a guess. If so, that is doable. However, your formula will not work for this since it performs no calculations.

If my guess is right, you can try this (assuming columns A, B and C):
[tt]
SchedStart Arrival early/late
1:00 AM 12:50 AM 0:10[/tt]

where the the formula in C2 is =a2-b2 and the formatting is h:mm.

Hope this helps
GS






[small][navy]*****************^*****************[/navy][/small]
[red]I think of the word processor as the pen's e-quill.[/red]

 

[draacor]

sorry i think i messed up when i said average mean. Let me explain in detail what i want. I cant for some reason think of the word to describe it. We have a standard where if someone is scheduled to work at 1:00 AM every day 5 days a week. We get the average login from another report. So for example, the average clock in for an employee is 1:03am. Another employee is 1:00am. Our standard is no greater than 1:05am. I have a report that calculates who our top performers are in the department. One of the categories is the clock in. As you can see the earlier they clock in the better. We also decided that this category is worth 12 percent of the overall score. What im trying to do is make it an automated calculation. In the past, i just took 1:00 am as the 100%. 12:59am would be 101% and 1:01am would be 99%. Then i take that percentage and times it by the weighted 12% to get the number i need. What i would like to do is take the manual entry out of the equation and have a formula that can do this for me. So i wanted to take 1:01 am or a variation of it and convert it to a number so i can do a weighted average on it.

 

[GSCaupling]

draacor,

Ignore my post above. It doesn't work for negative numbers.

HOWEVER, this does work: (a2-b2)*1440

Use a general or number format rather than a time format.

I failed to test the first idea for negatives - sorry.

GS


[small][navy]*****************^*****************[/navy][/small]
[red]I think of the word processor as the pen's e-quill.[/red]

 

[Gavona]

faq68-5827 may help your understanding.
1440 is 24hrs x 60minutes to convert the result of the math from decimal days to minutes.

I can't follow your logic - you will need to be clear when creating your formula.
1. If an employee is 10 minutes early one day and 10 minutes late the next then the average will be 0. However he has failed to meet your standard on 1 day.
2. "As you can see the earlier they clock in the better." So if he clocks in an hour early one day he gets extra points in your scoring system? And if he clocks in an hour late he gets the same number of negative points?

If you are clear yourself then fine. If you need help with the formulae then post some examples of different situations showing the required results as well as any descriptions and the formulae you are currently using (even if they don't quite work!)

Gavin

 

[draacor]

Thank you very much GS i think that will work for what i need. One more thing i was wondering if its possible. Can you take a number in excel like 301.00 and truncate it so it drops off the 30 in front of it so it only shows 1.00?

 

[bluedragon2]

For your last question:

(301.00/10-INT(301.00/10))*10

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GSCaupling]

draacor,

Are all of your numbers integers? Bluedragon2's forumula works perfectly (i.e., returns "1.00") if applied to 301.00 but returns "1.50" if applied to 301.5 (using two decimal places, per your example).

If your numbers are intergers, use Blue's formula and set the number of decimal places with formatting. Otherwise, it will probably be a bit more complicated.

GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[macropod]

Of course, depending on what you want, you could use:
=MOD(301.00,10)
or
=MOD(301.00,100)
The first returns 0-9.99[sup]•[/sup], the second returns 0-99.99[sup]•[/sup].

Cheers

[MS MVP - Word]