Subtraction/addition

[Cobra]

Hopefully someone can stay with me on this. I have colums A-H and 8 rows.

In colum C is the start time of my shift ex) 9am; in column D I have my
lunch out time ex)12 pm. In column E I have my lunch in time Ex) 12:30 and
in column F I have my Finish time ex) 5:30

What I want is in column G is the total number of hrs for that day and I do
not know the formula for this.

So it's 9 am start, 1/2 hr for lunch and quit at 5:30 equals a total time of
XXXX which should be column G

Hope someone can help
thank you

 

[Mike H]

try this in column G

=F2-C2-(E2-D2)

Ensure it's formatted as hh:mm

Mike

 

[Rick Rothstein \(MVP - VB\)]

You don't say exactly how you want your answer presented or if you will use
the result in another calculation. Mike gave you a way to display the value
as hours:minutes, but the number in the cell is still the fraction of a day.
So, if you want to use it in a calculation, you will have to remember to
multiply it by 24 within that calculation. However, if you want to display
the decimal number of hours directly (and in a form usable later on directly
in calculations), you could use this formula...

=24*(F2-C2-(E2-D2))

or, without the extra parentheses...

=24*(F2-C2-E2+D2)

Rick

 

[BoniM]

Wrote this formula for work so they don't have to worry about the am/pm thing
and because we need partial hours to be decimal - you may find it helpful:
=IF(D2-C2<0,D2-C2+0.5,D2-C2)*24+IF(F2-E2<0,F2-E2+0.5,F2-E2)*24
time should be entered in the hh:mm format.
Format G as number.

 

[Cobra]

Ok I appreciate all the help but unfortunatly none of them worked. I think
the hold up here will be in colums D & E if there is NO lunch taken, I am
entering "NO LUNCH" The formula appears to work fine when it's
=24*(F2-C2-E2+D2).

It's that NO lunch thing

 

[BoniM]

=IF(D2="NO LUNCH",(F2-C2)*24,(F2-C2-E2+D2)*24)
That should fix it...

 

[Cobra]

Bonim, again it works fine in the column(s) where there is a lunch time,
however it does not work where the "NO LUNCH" is entered.

It says "A value used in the formula is of the wrong data type"

Would it be possible to send you the work sheet that I am working on ?

 

[BoniM]

Sure - send it to boni at msn dot com...

Bonim, again it works fine in the column(s) where there is a lunch time,
however it does not work where the "NO LUNCH" is entered.

It says "A value used in the formula is of the wrong data type"

Would it be possible to send you the work sheet that I am working on ?