Convert and combine tasks from muliple time zones to local time zone

[suprsnipes]

Hi,

I'm a bit stuck and need some help with the following;

I am trying to create a sheet that allows for two time zones. Sydney, Australia and Chicago, United States. I have divided the day into 1hr blocks from 12am to 12am and listed in both local times a list of tasks I want completed and now I want to combine them into one time zone, my local time zone in Australia.

I have already got a formula that calculates the time difference.

This is where I am having trouble.

I have the start and end times in column A and B, which I have added the current date to so it updates daily in column C and D, then I have used a simple formula in column E for Sydney and column F for Chicago to return true or false if the current time falls within the two times in column C & D minus the time difference between Sydney and Chicago (which is currently 15hrs) but the problem with this is because of the large time difference I don't get a true in column F for Chicago until the time in Chicago has passed 12am being that the dates are now the same in column C & D. Not sure how to make this work.

I would appreciate any help, thanks.
suprsnipes