Convert UTC or AEST to local time precisely

Hello. I am struggling to find a formula that will convert UTC time to local Australian time, taking into account whether the date/time falls within Australian Eastern Standard Time (+10) or Australian Eastern Daylight Time (+11).

I have tried so many different variations from multiple sources and i'm not winning. I'm a bit frustrated and need a beautiful mind to help me.

I need the formula to recognise the following:
1st Sunday in October at exactly 2:00am - switch to daylight saving time (i.e. forward one hour)
1st Sunday in April at exactly 3:00am - switch to eastern standard time (i.e. back one hour)
I also need the formula to adapt to all years, past and future.

I'm not sure why none of the formulas suggested on many many forums haven't worked. Possibly because the UTC date falls 10 / 11 hours behind, making the conversion time a Saturday afternoon, not a Sunday morning. I'm thinking it would be easier to do it in two steps and convert to AEST first, then use a formula to recognise if it falls within specified time zone range.

I have attached a spreadsheet with columns for all relevant time zones and a column for what the local time should equate to.

Please help.

Based on your idea, and using new function available in Excel 365 which allows for not using dummy columns, but do multi-step calculations in one cell:
You may use such solution in G2 (and copy down):

Formula:

Please Login or Register  to view this content.

Note that while formula is rather long, the addres of A2 (source date and time) is used only once, so formula could be easily copied elsewhere and source cell has to be changed only in one place.

Or let's write this formula in more clear way:

Formula:

Please Login or Register  to view this content.

So first AEST is calculated,
then first sundays of April and October at 2am (in AEST)
then 1 hour is added to AEST if AEST converted date is "on winter side": before 1st Sun Apr or after 1st Sun Oct, .

Just in case - your test file contained only full hours.
Check the formula if it works fine also on 4 moments:
1st Sunday in October at 1:30am and 1st Sunday in April at 2:30am and two cases one hour later than these two.

And as for my input: you sugessted the way to go. I just offered a vehicle to go that way :-)


Final comments: Thanks for marking threasd as SOLVED. If the test from 1st paragraph of this post go wrong, mark thread as unSOLVED again. It would be lovely if you could also click on a star in lower-left corner of my post (and accept in the pop-up window) to add me some reputation.

Glad to hear so.

If you encounter problems with macro, try forum again :-)

But for such case, I'd suggest starting a new thread and only provide a link to this one.

BTW. LET formula by it's structure is perfectly well suited to be used as a starting point for macro, as we have variables calculated and then resused and finally the main calculation based on previously found variables.