creating formulas when data is missing - how to use another cell

user_19828015 0
Viewed 23

I'm not an excel pro so use basic language... :)

I've inherited a sheet which has been filled in manually so far but want to at least bring some cells into the 21st century.

D1 =TODAY()

F4: start date (manual input, short date)

G4:  end date (manual input, short date)

H4 =DATEDIF(F4,G4,"m")

I4 =@IFS(H4>12,"12+ Months",H4>=6,"6-12 Months",H4>=3,"3-6 Months",H4>0,"0-3 Months")

Problem -

I need to know what I4 is 'now' if it doesn't have an end date but, if G4 is blank, then I get #NUM! in both H4 and I4

How can use 'today() as an alternate if G4 is empty

if-statement excel
1 Answers

You can check that in H4 actually, replacing G4 with an IF condition

  • if G4 is blank ---> ISBLANK(G4)
  • then use today ---> today()
  • else use G4
 H4 = DATEDIF(F4,IF(ISBLANK(G4),today(),G4) ,"m")
user_26926880
Related