calculate shift duration

**jeffbrown1966** · 04-28-2010, 11:51 PM

I need to subtract .50 from a cell if it is over 6.00 I have a formula but can not get it to work

=IF(H6="P",G6+12-F6,IF(H6="A",G6-F6,(IF(I6>6,I6-0.5,(I6/24)))))

IN OUT
9.00 5.00 P would equal 8 hours I would like it to subtract .50 from the 8 hours

Thank you

**protonLeah** · 04-29-2010, 12:11 AM

for the sample provided:

=IF(B1+(12*(A1>B1))-A1>6,B1+(12*(A1>B1))-A1-0.5,B1+(12*(A1>B1))-A1)

where 12*(A1>B1) if the out time is less than the end time, then A1>B1 returns TRUE OR "1" else "0"

**jeffbrown1966** · 04-29-2010, 09:13 AM

[QUOTE=protonLeah;2297008]for the sample provided:
CODE]=IF(B1+(12*(A1>B1))-A1>6,B1+(12*(A1>B1))-A1-0.5,B1+(12*(A1>B1))-A1)[/CODE]
where 12*(A1>B1) if the out time is less than the end time, then A1>B1 returns TRUE OR "1" else "0"[/QUOTE

This one worked great until there was a 12 hour shift I tried the >= but it did not work

**zbor** · 04-29-2010, 01:59 AM

How about this:

=IF(MOD(H6-G6,1)>1/4,MOD(H6-G6,1)-1/48,MOD(H6-G6,1))

**DonkeyOte** · 04-29-2010, 04:14 AM

If we assume the values are integers rather than true time - then adapting zbor's suggestion:

=MOD(G6-F6,12)-0.5*(MOD(G6-F6,12)>6)

Per forum rules please retitle your thread though to something more appropriate - eg "calculate shift duration" (this helps others with similar questions)