CODEWITHSUNDEEP
google-sheetsformula
Dum Acco
Dum Acco

Reputation: 15

Replacing the blank cells with specific value

I'm trying to change the blank cells into specific value of 16. However, the value it shows is like 44877. I tried changing the format, but it won't change.

The link is here: sample sheet

Thank you so much

Upvotes: 0

Views: 978

Answers (3)

doubleunary
doubleunary

Reputation: 18794

Use isblank(), like this:

=arrayformula( 
  ifs( 
    isblank(A2:A), 16, 
    today() - A2:A > 1, today() - A2:A, 
    today() - A2:A < -1, 0, 
    true, iferror(1/0) 
  ) 
)

In the event the cells you call "blank" are actually not blank but contain a formula that yields zero-length text strings "", you need to consider that zero-length text strings are different from truly blank values.

To detect both zero-length text strings and truly blank values, replace isblank(A2:A) with not(len(A2:A)).

44877 is the dateserial value for today, 12 November 2022. See this answer for an explanation of how date and time values work in spreadsheets.

Upvotes: 1

player0
player0

Reputation: 1

try:

=INDEX(LAMBDA(a, t, 
 IF(a="",  16, 
 IF(t-a >  1, t-a-1,
 IF(t-a =  0, t-a, 
 IF(t-a < -1, 0, )))))(A2:A, TODAY()))

enter image description here

Upvotes: 0

Mart&#237;n
Mart&#237;n

Reputation: 10187

@doubleunary option works, obviously! As she/he suggested, the option where A is blank should be first. You're missing some scenarios: first you have >1 and <-1, if you want those values to be considered you should add an equal: >=1 <=-1

And also the one in which it is the same date as Today () -- so you should set first if A2:A is empty, then if it is equal, and then if its greater or smaller:

=ArrayFormula((IFS(isblank(A2:A), "16",TODAY()=A2:A, "Is today",TODAY()-A2:A>=1, TODAY()-A2:A, TODAY()-A2:A<=-1, "0")))

Upvotes: 0

Related Questions