IF and VLOOKUP to calculate time zone times

[Darren_workforce]

Good day,

I am struggling to combine IF and VLOOKUP statements to calculate times based on a time zone. On Data tab I have punch in times and all times are Eastern time due to the timeclock software. I'd like to locate the name of each rep in the Location tab, search Column C to find out their time zone, and then based on that information, subtract the appropriate amount of time from the punch time. (EX: Bob Roberts punched at 10:00am Eastern. Since he lives in Texas and is Central time, the punch time would have 60 minutes subtracted and return a result of 09:00am.) I have nobody in Hawaii or Alaska so I am not currently concerned with those time zones, However if we do hire reps from those states, I would plan on using "Alaska" and "Aleutian" as the Column C designations.

=IF(ISNA(VLOOKUP(B9,'[CST States - Time Zones.xlsx]Sheet1'!$A:$C,3,FALSE))="eastern","not eastern",D9)

the above is all I've come up with. Where the "not eastern" is would be where it should subtract the appropriate number of minutes but I'm failing to get it to work.
1) What am I missing?
2) How do I factor in all US time zones for this within one formula?

TYIA

A	B	C
John Smith	North Carolina	Eastern
Bob Roberts	Texas	Central
Allen Jones	Florida	Eastern
Will Smith	New Mexico	Mountain

 

[etaf]

can you use a table with the states and calculation and then use a lookup
that way - you are just changing a table to add items when they come - rather than changing an IF statement

how do you know the state the name is in, looks like a table anyway -

maybe in column D add the time difference - will they all be subtracted

and just apply a lookup to column D

will you have duplicated names at all ?

 

[Darren_workforce]

can you use a table with the states and calculation and then use a lookup
that way - you are just changing a table to add items when they come - rather than changing an IF statement

how do you know the state the name is in, looks like a table anyway -
HR provides us with the states. Or I ask the team member during their training.
maybe in column D add the time difference - will they all be subtracted
Great idea! I added column D and setup either 0,60,120 or 180 depending on the time zone. I will say yes it will always be a subtraction as there are not currently any employees further East than Eastern Time Zone. So that is true as long as we don't hire anyone further East.
and just apply a lookup to column D
Another great idea! I was overthinking things.

will you have duplicated names at all ?

I don't believe so. If there is, HR usually provides a middle initial. So far, I have not come across a duplicate name. OK I appreciate you talking me through this. I setup Column D with the number of hours difference from Eastern if the rep lives further west. Then I'm subtracting the time difference from the original punch time regardless and since Eastern generates zero hours, it doesn't change. The rest I can easily add back into the original table. I think that did it. Thank you very much for helping my morning brain think this through @etaf

 

[etaf]

you are welcome