mercredi 29 mai 2019

IF statement using cell formatted value

In Google Sheets, how can you use the IF statement using the output of a cell's formatted value?

I've created a Google Sheet with a date value in B1. The cell is formatted using the date format. B1 is then copied across to the right so that it increments the days of the month.

https://i.imgur.com/KzhwHUw.png

https://i.imgur.com/d8S4Cco.png

In B2, based on the B1 date, I want to display the day value (ie Thu, Fri, Sat etc.). Similarly to the first step, I set the date format under Format > Number > More formats > More date and time formats, then from the select box, choose the day and I want to display the "Tue" date format.

https://imgur.com/4Owz7jl.png

Then in B8, I want to display an amount of 30 every Thursday, otherwise, show 0.

https://i.imgur.com/s5mTcct.png

This is where it doesn't work. Does it seem like the rendered cell's value can't be used in an IF statement? My desired output is that every the value in row 2 is a 'Thu' display 30.


As a side note, I jumped on Google support and asked this same question. Even though they are technical support, I thought I'd give them a try anyway, after all, I am paying for GSuite.

This is the formula they came back to me with: =if(B2=B1, "30", "0").

Of course, this formula will work in B8, because B2 is equal to B1 in the actual cell value, it doesn't take into account the formatted cell value. This formula will fail the output we need as it will always display 30 instead of only then row 2 is 'Thu'.

So essentially, is there a function in Google Sheets for a rendered cell value? Or another solution around this?

Note: I do NOT want to use any scripting to get this to work.

Aucun commentaire:

Enregistrer un commentaire