The Scenario
You manage customer success for a SaaS product used across 14 time zones. Your team runs automated check-in emails that are supposed to land at 9 AM local time for each client. The problem: the email platform takes a time zone string, not a country code, and your client database has IP addresses — not time zones.
Someone added a "timezone" column to the workbook two months ago and left it blank. You just noticed when a client in Tokyo replied asking why they keep getting emails at 2 AM.
The bad version:
- Try to write a formula that maps country codes to IANA time zones. Find that dozens of countries span multiple time zones and there is no clean 1:1 mapping.
- Look up each of the 1,200 client IPs individually on a geolocation site. Manually copy the time zone name into column B.
- Get through 60 rows before realizing you have been copying "America/New_York" for clients whose IPs are actually in Chicago.
Your client retention report goes to leadership quarterly. The time zone enrichment should have been done before the check-in sequence launched, not six weeks into sending emails at the wrong hour.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Excel workbook. It reads the client IP column, calls ipdata.co for IANA time zone data on each row, and writes the results into the columns your email platform needs.
For each IP address in column A, use ipdata.co to look up the IANA timezone name and UTC offset and write them to columns B and C.
What You Get
- Column B: IANA time zone identifier (e.g., "America/Chicago," "Asia/Tokyo," "Europe/London")
- Column C: UTC offset string for each IP (e.g., "-06:00," "+09:00")
- Every client IP covered — even IPs in multi-timezone countries get the specific zone, not just the country's primary zone
- Blank rows noted so no client silently gets the wrong time zone
What If the Data Is Not Quite Ready
You need a count of clients per time zone to help with team scheduling
Fetch timezone data for every IP in column A using ipdata.co and write the timezone name to column B. Then group by timezone and count clients per timezone in a summary table starting at row 1205.
Some clients have multiple accounts and the same IP appears more than once
Deduplicate column A by IP address. Look up the IANA timezone name and UTC offset for each unique IP using ipdata.co and write to columns B and C. Then use the results to fill the full workbook via a match on column A.
The email platform also needs a continent label for routing rules
For each IP in column A, fetch the IANA timezone name, UTC offset, and continent name from ipdata.co and write them to columns B, C, and D.
Kill-chain: deduplicate clients, enrich time zones, assign send-windows, and flag missing data
Deduplicate client rows by email address (column B). For each unique client, look up the IANA timezone from the IP in column A using ipdata.co and write to column C. In column D, write the recommended send window (e.g., "09:00 local") based on the UTC offset in column C. Flag any row where the IP returned no timezone data as NEEDS REVIEW in column E.
One prompt takes you from raw IPs to a fully scheduled, flagged client list ready for the email platform.
Try It
Get the 7-day free trial of SheetXAI and open your client IP workbook, then ask SheetXAI to append IANA time zones from the IP column using ipdata.co. See also geo-segmenting session IPs by region or the ipdata.co integration overview.