What is the date format applied when using a data placeholder in the message body of an alert?

Sharon F.

I have a date field in the sheet and have formatted the date column with dd-mmm-yyyy. This format does not flow through with alert messages. Please advise what the standard format is that is applied as I would like to include information in the body of the message that informs this as my stakeholders are from all over the world and getting confused with whether to read it as dd/mm/yy or mm/dd/yy.

Genevieve P.

Hi @Sharon Fernandes

In my testing, the email comes through as mm/dd/yy when using placeholders. However if you're finding users are confused by this, you could create a helper column that uses a formula to spell out the date, then use this helper column as your placeholder instead of a date-type of column.

For example, in my sheet I have a formula that looks at the MONTH of the date, and depending on the month number returns the Text of that month (ex, =IF(MONTH([Start Date]@row) = 1, "January",)

Then I add the DAY of that date and the YEAR of that date to the text as well, like so:

+ " " + DAY([Start Date]@row) + ", " + YEAR([Start Date]@row)

The formula ends up being a little long because I have 12 IF statements to check which Month the date is, but it ends up producing the text I need for any month or date.

Full formula:

=IF(MONTH([Start Date]@row) = 1, "January", IF(MONTH([Start Date]@row) = 2, "February", IF(MONTH([Start Date]@row) = 3, "March", IF(MONTH([Start Date]@row) = 4, "April", IF(MONTH([Start Date]@row) = 5, "May", IF(MONTH([Start Date]@row) = 6, "June", IF(MONTH([Start Date]@row) = 7, "July", IF(MONTH([Start Date]@row) = 8, "August", IF(MONTH([Start Date]@row) = 9, "September", IF(MONTH([Start Date]@row) = 10, "October", IF(MONTH([Start Date]@row) = 11, "November", IF(MONTH([Start Date]@row) = 12, "December")))))))))))) + " " + DAY([Start Date]@row) + ", " + YEAR([Start Date]@row)

You can see above my formula what it looks like in the cell itself. Then you can make this a Column Formula so it's automatically applied to every row, and use this in your email instead. Would this be better for you?

Cheers!

Genevieve

Paul Newcome

@Genevieve P Haha. No worries. I actually prefer yours as it makes things much clearer by spelling it all out. Even writing dates by hand I generally use "04 May 2021" just so that it is clear exactly what the date is.

The biggest reason for my suggestion above is if you want to mimic exactly the formatting of the actual date column.

Genevieve P.

Hi @Sharon Fernandes

In my testing, the email comes through as mm/dd/yy when using placeholders. However if you're finding users are confused by this, you could create a helper column that uses a formula to spell out the date, then use this helper column as your placeholder instead of a date-type of column.

For example, in my sheet I have a formula that looks at the MONTH of the date, and depending on the month number returns the Text of that month (ex, =IF(MONTH([Start Date]@row) = 1, "January",)

Then I add the DAY of that date and the YEAR of that date to the text as well, like so:

+ " " + DAY([Start Date]@row) + ", " + YEAR([Start Date]@row)

The formula ends up being a little long because I have 12 IF statements to check which Month the date is, but it ends up producing the text I need for any month or date.

Full formula:

=IF(MONTH([Start Date]@row) = 1, "January", IF(MONTH([Start Date]@row) = 2, "February", IF(MONTH([Start Date]@row) = 3, "March", IF(MONTH([Start Date]@row) = 4, "April", IF(MONTH([Start Date]@row) = 5, "May", IF(MONTH([Start Date]@row) = 6, "June", IF(MONTH([Start Date]@row) = 7, "July", IF(MONTH([Start Date]@row) = 8, "August", IF(MONTH([Start Date]@row) = 9, "September", IF(MONTH([Start Date]@row) = 10, "October", IF(MONTH([Start Date]@row) = 11, "November", IF(MONTH([Start Date]@row) = 12, "December")))))))))))) + " " + DAY([Start Date]@row) + ", " + YEAR([Start Date]@row)

You can see above my formula what it looks like in the cell itself. Then you can make this a Column Formula so it's automatically applied to every row, and use this in your email instead. Would this be better for you?

Cheers!

Genevieve

Sharon F.

I had this thought as well but my sheet is already quite formula heavy in terms of the number of columns with complex formula and I’m concerned about the performance being even more impacted by adding another one with 12 IF statements. I also have more than one date column. Should I not be worried too much about an impact on performance?

Paul Newcome

Additionally, if you wanted to just keep it as dd/mm/yy, you could enter the below as a column formula in a text/number column then use it as your column for the placeholder:

=[Date Column Name]@row + ""

This will convert it to a text value which has no actual day, month, or year value for date formatting to rearrange.

Of course if you have international users that use different date formats, I would suggest Genevieve's approach of spelling it out.

Genevieve P.

Thanks @Paul Newcome! That's a much cleaner formula, haha.

Sharon, my formula shouldn't impact sheet performance too much as it's using @row, however if you have a high number of other columns using formulas I would suggest using Paul's one instead as it's much more simple for the sheet to manage.

Paul Newcome

@Genevieve P Haha. No worries. I actually prefer yours as it makes things much clearer by spelling it all out. Even writing dates by hand I generally use "04 May 2021" just so that it is clear exactly what the date is.

The biggest reason for my suggestion above is if you want to mimic exactly the formatting of the actual date column.