I searched high and low for this and couldn’t find a good answer, but figured it out on my own so sharing to help others.
Suppose you have a Date column in monday which contains a date and time, e.g. an order date. You want to create an automation that calculates another date and time plus or minus some number of hours. This is surprisingly impossible using Monday’s built in automations, but you can do it using the General Caster extension available in the monday app store here
The formula you need is:
=TEXT((DATEVALUE({item's Order Date})+TIMEVALUE({item's Order Date})+TIMEVALUE("1:00")),"YYYY-MM-DD HH:MM:SS")
where
{item’s Order Date} is a Date column including timestamps that represents the date and time the order was placed
and
TIMEVALUE("1:00")
is to add 1 hour to that date/time.
When you use General Caster to cast this formula result to another date column (e.g. perhaps a fulfilment deadline), Monday will handle displaying it in the correct timezone automatically even if the raw data you see when testing is in UTC.
Hope this helps someone!