DAY() fails with looked-up date

Topic Labels: Formulas
1187 2
Showing results for 
Search instead for 
Did you mean: 

I was idiot-checking some advice I was about to give (meaning I was the idiot needing to be checked) when I ran into this. There’s an earlier mention of this problem from January 2016, so I am going to assume that was fixed long ago and something similar has broken since then.

In the following screenshot, {Date} is a looked-up date field, while {Month}, {Day}, and {Year} are formula fields that simply call the function indicated with {Date} as the parameter. (That is, {Month} is a formula field with the formula MONTH({Date}), and so on.) Note the results for {Day}.


I then modified {Date}'s configuration, changing it to a date field. Here’s the result:


2 Replies 2
10 - Mercury
10 - Mercury

I can’t see why these would work in the first place, as the lookup should always produce an array with possibly multiple values.

Inconsistent behavior?

That’s what i assumed, as well – in fact, what led me into this in the first place was responding to someone to say a looked-up date wouldn’t actually be a date – and then thinking i should verify that before clicking ‘reply’. Much to my surprise, I discovered the date was a date — most of the time.