Date Format "dd" Should be the Day of the Month

In keeping with compatibility with Excel and Google Sheets, when using the TEXT function, the format “dd” should return the day number for a date instead of the first two letters of the weekday name.

For example, TEXT(date,“yyyy-mm-dd”) for April 23, 2020 should be 2020-04-23, and for April 8, 2020, it would be 2020-04-08. Currently, the function is returning 2020-04-Th.

Yes, agreed. Some TEXT function improvements are in our TODO. We’ll work on this.

Tracking this as Issue 001536. Will update the thread when we have progress we can share.

This might be related to text, or a separate issue, but =LEFT(TEXT(date,“ddd”),1) should return the first letter of the day of the week, but it’s currently returning a numeric value.

Yes. Will fix as part of 001536. Pretty soon we’ll be doing a refresh with a bunch of TEXT() function fixes

  1. TEXT(date,“yyyy-mm-dd”) for April 23, 2020 should be 2020-04-23
  2. =LEFT(TEXT(date,“ddd”),1) should return the first letter of the day of the week

Both fixes are now available in production.

Excellent. LEFT(TEXT(date,“ddd”),1) now works when date is a manually entered date.

However, if A1 = date, and A2 is a formula like =date+5, then =LEFT(TEXT(A2,“ddd”),1) is returning the first character displayed in A2 (rather than the first letter of the day of the week as it does in Excel and GS). For example, if A2 is formatted as “July 4, 2020”, then the formula is returning “J”, or if it is formatted as “7/4/2020”, it is returning “7”.

Then, I realized that LEFT(date,N) returns the first N displayed characters after formatting is applied. In Excel, if cell A1 is a date, LEFT(A1,3) returns the first 3 digits of the numeric value. However, in Google Sheets, if A1 is a date, LEFT(A1,3) returns the first 3 displayed characters.

This is actually kind of interesting, because until now I wasn’t aware of any formulas that could return values based on the number formatting of a cell. This means that for functions like LEFT, RIGHT, SUBSTITUTE, MID, etc., the format of the cell is included in the calculation chain. I’ve always thought that cell formatting was applied after or independent of the calculation chain, but it turns out that in Google Sheets and Spreadsheet.com, you can use these functions to determine the number formatting. Interesting.

Perhaps this is why Google Sheets has not made it possible to use conditional formatting to modify the number formatting of a cell (an annoyance I have run up against many times). In Excel, conditional formatting can be used to change the number format of a cell. This fundamental difference between GS and Excel may continue to be a problem with compatibility of some spreadsheets.

Yes. I noticed it too - LEFT(TEXT( date ,“ddd”),1) - does not work when date is =TODAY().
Tracking as 001763

I’ll let @msiraj answer the nuances of type inference & promotion :slight_smile: in relation to numbers, dates and formatting.

Yes, the following TEXT category functions always works on top of formatted value:

  • SUBSTITUTE
  • PROPER
  • FIND
  • RIGHT
  • LEFT
  • SEARCH
  • MID
  • TRIM
  • EXACT
  • LEN
  • UPPER
  • LOWER,
  • TEXT

Also, formatting instructions are carried over as part of formula-evaluation. That is, a formula-cell computing AVERAGE of a currency column will re-evaluate to reflect a formatting change when the referenced currency-column formatting changes. Same is applicable even for formulas with conditional flows.

Regarding conditional formatting, will make note of your inputs in this regard when we add this feature.

a formula-cell computing AVERAGE of a currency column will re-evaluate to reflect a formatting change when the referenced currency-column formatting changes.

That is interesting, and I can see how that would be useful for some types of functions. For me, personally, I’m finding the inheritance of format from the references within a formula to be fairly annoying. When the result is what I expect, I don’t really think twice, but quite often the result is not what I expect. Most of the time, I’m able to change the data type of the formula to format the resulting number how I want it, however that is not the case with a Column Formula data type. If I can’t figure out how to manipulate the formula to get the correct number format, I can’t use a Column Formula data type.

Example 1: A1=$25, A2=50, A3 = A1/A2 ... I would expect A3 to be 0.50, and I'd probably change that to 50% if I wanted to show a percent instead of a ratio, but the spreadsheet assumes a format and displays $0.50. When using a Column Formula, I haven’t figured out any way to make $0.50 display as a ratio or percent.

Example 2: A1=25%, A2=$100, A3 = A1A2 … I most likely want the result to be $25.00, but instead the result is 2500.00%. I have to change the formula to A2A1 if I want the result to be $25.00.

Example 3: A1=$2000 total budget, A2=20 days, A3=$25 per hour. A4 = A1/A2/A3 with the result in hours per day. However, the result shows $4. I tried changing this to 1/A2*A1/A3, but it still shows $4. I have to change the data type to number to get rid of the dollar sign. I haven’t figured out how to make this work with a Column Formula.

This issue is related to another post:

Thanks for the inputs.

Regarding column-formulas, we have a backlog-item to add support for defining display-type and/or formatting details for column formulas as well.

Regarding type-promotion in general, as correctly pointed out, there are exceptions to the type promotion rule.
A1=$100,A2=10,

A3=A1/A2, this should evaluate as 10. For DIV operator, there should not be any type promotion. This is currently a defect in SSDC and is being tracked as BUG# 001583.

Regarding,
Example 2: A1=25%, A2=$100, A3 = A1 X A2

Here A3 formula cell will inherit type only if it is an automatic-cell. User can explicitly set its type as Currency to ensure it evaluates and displays as Currency instead of Percent.

Additionally, we have complex types like Attachment, Relationships etc., which when used as part of formula-expressions will be normalized to number/string/boolean literals accordingly. Only in case of direct reference to such complex types, will the cell data carry over as is.

In summary, the type-promotion rules are defined such that by default give comprehensible behavior in most of the cases. However, are flexible and can be overridden to behave otherwise.

2 Likes

This issue should now be addressed by virtue of the new Formatting options available in the Column Formula data type dialog. Details in our September 18th release notes.

This is great. I just tested the new formatting options for the column formulas. Love it!

3 Likes