FORMULA

[Guest]

I am trying to create a formula that will subtract 14.5 hours from the total
hours in one cell if the date in cell J4 is greater than the date in cell F4.
Can anyone help?

Here is what I was trying, please don't laugh, I'm pretty new at excell

=IF(J4>F4,=+(J4+K4)-(F4+G4)-14.5,=+(J4+K4)-(F4+G4))

Note: =+(J4+K4)-(F4+G4) is the formula that is calculating the total that I
want to subtract 14.5 hours from.

 

[vpanang]

=IF(J4>F4,(J4+K4)-(F4+G4)-14.5,(J4+K4)-(F4+G4))

this should work...

 

[Peo Sjoblom]

If the values are excel time values like 8:30 etc

=IF(J4>F4,(J4+K4)-(F4+G4)-(14.5/24),(J4+K4)-(F4+G4))

if decimal values like 8.5

=IF(J4>F4,(J4+K4)-(F4+G4)-14.5,(J4+K4)-(F4+G4))

having said that, what are in F, G, J and K?

Are you subtracting hours worked after midnight?

=MOD((end-start)-(in-out),1)

or

=end-start+(start>end)-(in-out)


where end is end of work, start is start of work, in is end of break and out
start of break


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Sandy Mann]

Just take the =+ out of your formula

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Oh, thank you so much! The first formula you provided solved my problem! I
am shocked that I was so close too! You asked: "having said that, what are
in F, G, J and K? Are you subtracting hours worked after midnight?" F
contains the date a product left our shop, G contains the time it left our
shop, J & K contain the date and time it came back. Our working hours are
7:00am to 4:30pm, we wanted to calculate the total hours that it took the
product to be returned for final processing but from 4:30pm (end of shift) to
7:00am (next day shift begins) don't count in the total holding time. So I
wanted to subtract the 14.5 hours that don't count if say we sent the product
out at 2:30pm and didn't get it back until 10:30am the next day. Whew! Hope
that makes sense! Again thanks so much for your help!

Tania

 

[Guest]

Hi! I am still working on my spreadsheet that you helped with before. I
have realized that if a product comes in on a Friday afternoon I do not want
to count the hours of the weekend when we aren't working. I want to begin
counting the time from 7am Monday morning again. Is there a way to do this?
Can excel recognize that something has come in on a Friday and subtract the
total weekend hours... I suspect that it can since it does recognize what day
of the week a date is in any given year.

With your help, here is what I have now:

=IF(J4>F4,(J4+K4)-(F4+G4)-(14.5/24),(J4+K4)-(F4+G4))