Add time but ignore period of time

[botty1963]

Hi, I have some data where I an pulling out the hour that an incident was logged, so simply using =HOUR. I then want to add a number of hours to this log hour, so for example if an incident was logged in hour 15 (3pm), and I want to add 4hrs, it gives me hour 19 (7pm); fairly simple. However, I need it to ignore a window of time. So, if an incident was logged in hour 22, and I want it to add 4hrs but ignore hours 23 to 7 (through the night), I want it to show hour 10, ie 1 hr from 22-23, and then 3 from 7 to 10. Any suggestions?

[Ash_Maverick]

Can you upload a file with examples.

[Phuocam]

Try this ...

=MOD(A1+4+(A1>18)*8,24)

[botty1963]

Many thanks for that. It seems to work in some instances but not where the incident has been logged after midnight, in those instances it just added the 4hrs to the log hour.