Time sheet template military hours problem
 

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!

Time sheet template military hours problem
 

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 FormatCellsNumber
tabselect General.

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


--
Biff
Microsoft Excel MVP


"Cheryl" wrote in message
...
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!

Time sheet template military hours problem
 

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 he

=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

Time sheet template military hours problem
 

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 FormatCellsNumber tabTime
Select the 1:30 PM format
OK

If I replace the formula with the one you wrote he
=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.

--
Biff
Microsoft Excel MVP


"Cheryl" wrote in message
...
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 he

=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

Time sheet template military hours problem
 

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

Thanks!

"T. Valko" wrote:

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.

Time sheet template military hours problem
 

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!

--
Biff
Microsoft Excel MVP


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

Thanks!

"T. Valko" wrote:

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.

Time sheet template military hours problem
 

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

regards
FSt1

"Cheryl" wrote:

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

Thanks!

"T. Valko" wrote:

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.