Converting from UTC to Local Time - Dynamic Formula

Question

I have an excel spreadsheet that I'm using to help map team coverage. I have teams in four different time zones. We are using UTC as our baseline time, however, I want to create a formula that will automatically update the time for that person's time zone. Example:

When a person enters in their UTC time zone in E7, I want the local time displayed for midnight UTC displayed in C3. 

I tired using =C2+1-TIME((ABS(T8)),0,0), but that doesn't work for negative UTC coordinates, such as UTC-7.

Any thoughts? Thank you.

Answer 1

In C3, put following formula and drag to right -

=C2+IF($E$7<0,(24+$E$7)/24,$E$7/24)

Format your row 3 appropriately to show AM/PM