Last day of last month:
SET_TIMEZONE(
DATEADD(
SET_TIMEZONE(
TODAY(),
'America/Phoenix'
),
-DATETIME_FORMAT(
SET_TIMEZONE(
TODAY(),
'America/Phoenix'
),
'D'
),
'day'
),'
America/Phoenix'
)
If you have that formula in a Field called “Last Month”, then for 2 months ago you could say:
SET_TIMEZONE(DATEADD({Last Month}, -1, 'month'), 'America/Phoenix')
And for 3 months ago:
SET_TIMEZONE(DATEADD({Last Month}, -2, 'month'), 'America/Phoenix')
Make sure you have “Use the same time zone (GMT) for all collaborators” unchecked in the formatting options so that your timezone is always used via the SET_TIMEZONE functions. Without those functions, you sometimes get a date that is off by 1 day. Alternatively, you could make sure that every single date field in your table has the “Use same time zone (GMT)…” checked on.
Anywhere you see ‘America/Phoenix’, you’ll have to replace it with your timezone from this list:
Last day of last month:
SET_TIMEZONE(
DATEADD(
SET_TIMEZONE(
TODAY(),
'America/Phoenix'
),
-DATETIME_FORMAT(
SET_TIMEZONE(
TODAY(),
'America/Phoenix'
),
'D'
),
'day'
),'
America/Phoenix'
)
If you have that formula in a Field called “Last Month”, then for 2 months ago you could say:
SET_TIMEZONE(DATEADD({Last Month}, -1, 'month'), 'America/Phoenix')
And for 3 months ago:
SET_TIMEZONE(DATEADD({Last Month}, -2, 'month'), 'America/Phoenix')
Make sure you have “Use the same time zone (GMT) for all collaborators” unchecked in the formatting options so that your timezone is always used via the SET_TIMEZONE functions. Without those functions, you sometimes get a date that is off by 1 day. Alternatively, you could make sure that every single date field in your table has the “Use same time zone (GMT)…” checked on.
Anywhere you see ‘America/Phoenix’, you’ll have to replace it with your timezone from this list:
Pretty clever, @Jeremy_Oglesby.
I was going to suggest something like
DATEADD(
SET_TIMEZONE(
DATETIME_PARSE(
MONTH(
TODAY()
)&
'/1/'&
YEAR(
TODAY()
),
'M/D/YYYY'
),
'America/Los_Angeles'
),
-1,
'd'
)
but I’d never have thought of subtracting today from today to get the end of last month.
Last day of last month:
SET_TIMEZONE(
DATEADD(
SET_TIMEZONE(
TODAY(),
'America/Phoenix'
),
-DATETIME_FORMAT(
SET_TIMEZONE(
TODAY(),
'America/Phoenix'
),
'D'
),
'day'
),'
America/Phoenix'
)
If you have that formula in a Field called “Last Month”, then for 2 months ago you could say:
SET_TIMEZONE(DATEADD({Last Month}, -1, 'month'), 'America/Phoenix')
And for 3 months ago:
SET_TIMEZONE(DATEADD({Last Month}, -2, 'month'), 'America/Phoenix')
Make sure you have “Use the same time zone (GMT) for all collaborators” unchecked in the formatting options so that your timezone is always used via the SET_TIMEZONE functions. Without those functions, you sometimes get a date that is off by 1 day. Alternatively, you could make sure that every single date field in your table has the “Use same time zone (GMT)…” checked on.
Anywhere you see ‘America/Phoenix’, you’ll have to replace it with your timezone from this list:
Hi @Jeremy_Oglesby / all,
I am trying to get the formula for a similar problem - I want to display 5pm on the last day of the current month.
Example if today is 10 may 2018, I want to display ‘May 31, 2018 05:00pm’
Any help much appreciated.
thanks
Hi @Jeremy_Oglesby / all,
I am trying to get the formula for a similar problem - I want to display 5pm on the last day of the current month.
Example if today is 10 may 2018, I want to display ‘May 31, 2018 05:00pm’
Any help much appreciated.
thanks
Assuming you always want this relative to “today”, whatever today may be, this will do it:
DATETIME_PARSE(
DATETIME_FORMAT(
DATEADD(
DATEADD(
TODAY(),
1,
'month'
),
-DAY(TODAY()),
'day'
),
'L'
)
& " 17:00",
'M/D/YYYY HH:mm'
)
If you are wanting this to evaluate relative to the date in a field in your table, just replace both instances of TODAY() in the formula above with a reference to your Date field. I don’t think you’ll have Timezone issues with this one since you are manually forcing the time in from a string, so no need to use SET_TIMEZONE().
