Excel 2007 : How to derive an SLA start Time from a created time based on a 4hour fix SLA

Hello

I have a problem where I am trying to calculate an SLA Start Time from a given Created Time, the formula I have come up with is;

=IF(F9="A",C9+TIME(1,30,0),IF(AND(E9>"19:30:00",E9<"23:59:59"),D9+1+"05:30",D9+"05:30")))

My columns are where Category A is for a call raised between 01:30 and 19:30 and B is 19:30 and 01:30 also if the created time is between 19:30 and 05:30 the following morning then the SLA start time must begin at 05:30

A1 B1 C1
Created Time Category SLA Start Time
21/04/2010 04:20 A 21/04/2010 05:30
21/04/2010 05:32 A 21/04/2010 05:32
21/04/2010 15:00 A 21/04/2010 15:00
21/04/2010 19:35 B 22/04/2010 05:30
22/04/2010 00:21 B 22/04/2010 05:30

The problem I have had is getting the various formulas to see that the SLA carrys over to the next morning after 19:30 and so change the date to reflect, excepting of course where the created time is already on that morning.

Please help.

Regards

Bazzman

I have improved on my formula but it still doesn't correctly determine appropriate time according to the date......

=IF(AND(MOD(C7,1)>=TIMEVALUE("05:30"),MOD(C7,1)<=TIMEVALUE("19:30")),C7,IF(AND(MOD(C7,1)>=TIMEVALUE("19:30"),MOD(C7,1)<=TIMEVALUE("23:59")),D7+1+E7,D7+"05:30"))

Solved ! I found the solution