Time sheet template military hours problem

[Cheryl]

Hi everyone,

I'm using the MS Excel online template numbered TC062071411033. In the cell
that calculates the hours worked from the time that the user inputs is a
formula as follows:

=IF((((D3-C3)+(F3-E3))*24)>8,8,((D3-C3)+(F3-E3))*24)

This formula only works properly if the time entered is formatted in
military format. I want to use standard am/pm hours instead. Any idea of
how to change the formula so it will work correctly with am/pm?

Thanks!

 

[T. Valko]

You can reduce that formula to:

=MIN(8,((D3-C3)+(F3-E3))*24)

In Excel time is stored as a fraction of a day. A day has a numeric value of
1 so time is a fractional part of 1.

12:00 PM is half a day so the numeric value for 12:00 PM is half of 1 or
0.5. The format used to display the time is irrelevant as to how the time is
calculated. The format is for *display purposes only*.

6:00 PM and 18:00 have the same numeric value, 0.750.

You can see the decimal value of the time by entering a time like 12:00 PM
then, with that cell still selected, goto the menu Format>Cells>Number
tab>select General.

So, if you're not getting the correct result something else is the cause.

 

[Cheryl]

Ok.. here's what the deal is.

Using this formula in the template:

=IF((((D3-C3)+(F3-E3))*24)>8,8,((D3-C3)+(F3-E3))*24)

If I enter the hours using military time this is what I get:

C3 D3 E3 F3 Total
8:00 11:00 12:00 18:00 8.00

If I enter the hours in this template using am/pm time this is what I get:

C3 D3 E3 F3 Total
8:00 11:00 12:00 3:00 -6.00


If I replace the formula with the one you wrote here:

=MIN(8,((D3-C3)+(F3-E3))*24)

.... and use military time then I get this:

C3 D3 E3 F3 Total
8:00 11:00 12:00 3:00 TRUE

or if I use am/pm time I get this:

C3 D3 E3 F3 Total
8:00 11:00 12:00 3:00 -6.00

Here's what I want. I want to input the time in am/pm format and have the
results look like this:

C3 D3 E3 F3 Total
8:00 11:00 12:00 3:00 6.00

 

[T. Valko]

If I enter the hours in this template using am/pm

time this is what I get:
C3 D3 E3 F3 Total
8:00 11:00 12:00 3:00 -6.00

Ok, but you *didn't* enter the time a AM/PM. You *have to include (type in)
the AM/PM*. If you don't then the entry defaults to AM. So, 3:00 is being
evaluated as 3:00 AM.

.....C3..............D3..............E3.............F3
8:00 AM...11:00 AM...12:00 PM...3:00 PM

Don't let Excel "decide" what format it thinks you want. Format the cells
the way you want them.

Select the range of cells
Goto Format>Cells>Number tab>Time
Select the 1:30 PM format
OK

If I replace the formula with the one you wrote here:
=MIN(8,((D3-C3)+(F3-E3))*24)
and use military time then I get this: TRUE

Hmmm....That's not possible! The formula returns a number not a boolean.

Here's a small sample file that demonstrates this:

xTime.xls 14kb

http://cjoint.com/?mngp2qsh68

It contains the formula I suggested and the one you posted. Notice that when
I entered the times I included the AM/PM.

 

[Cheryl]

Whoops! Well, dang. Shouldn't it just know that's what I'm doing? (JK!!)

Thanks!

 

[T. Valko]

Shouldn't it just know that's what I'm doing?

Well, they tried to program it to be "helpful" but it hasn't worked out so
good!

You're welcome!

 

[FSt1]

hi
i understand that they are working on it, but psychic software is not yet
available.

regards
FSt1