MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Time


Posted by Rob on December 31, 2001 7:19 AM

Hi,
I am trying to get a formula to do alot of things.
=IF((C9-B9)>7,(C9-B9)-1,IF((C9-B9)>5.5,(C9-B9)-0.5,(C9-B9)))

This is obviously wrong, but close.
This determines breaks on a scheduled time shift on a work schedule. (ex. over 7 hours(1 hour break), over 5.5 hours(.5 hour break))
I want to calculate the difference between two cells in a time format, using only time and not date format. I tried to use a simple subtraction and it didnt work, because I didnt want to use a date and time format so I used a decimal format.
Is there a way to do a side formula to figure out the time difference and then use that value(ex. X) to figure out the IF's that I need to determine. Or is there a way to use only a time format without the date(there is not enough room in my cell width on the worksheet for both) and then do the difference in a time format???

Please help
Rob


Posted by Barrie Davidson on December 31, 2001 7:23 AM

Rob, try using this:

=IF((C9-B9)*24>7,(C9-B9)*24-1,IF((C9-B9)*24>5.5,(C9-B9)*24-0.5,(C9-B9)*24))

Regards,
BarrieBarrie Davidson