combining ISBLANK with IF/ELSE

[davyjones]

Good day all!

The below code is for a calculated column.  At first, I wanted the column to check another set of columns, each with their own 'stage' and do basically a 'DATEDIFF' on them.  That worked just fine, but I did not factor into the issue that some of the date columns would be blank and that then returned a HUGE number.  What I would like to do, is just put an 'N/A' into the calculated column if the 'stage' is blank.  The below code doesnt return an error, but it also is not returning the 'N/A' that ive been wanting.  I know that you can only nest up to 7 'if' statements, but I think that I am at that level.  Any ideas?

=IF(ISBLANK(RequestState),"N/A",

     IF(RequestState="SUN",TODAY()-SUN_Initiated,

       IF(RequestState="DS",TODAY()-DS_Initiated,

          IF(RequestState="TechEffort",TODAY()-TechEffort_Initiated,

             IF(RequestState="CCB",TODAY()-CCB_Initiated,

                IF(RequestState="Release",TODAY()-Release_Initiated,

                     TODAY()-VR_Initiated))))))

[#2]

@davyjones Your formula looks fine to me. What is the return data type in calculated column settings? 

If it is "Date and Time", returning "N/A" values from formula might not work in this case. So, you have two possible ways to solve this problem:

1. Continue using "Date and Time" as return type & pass the default date value in case stage column ([RequestState]) is empty.
2. Use "Single line of text" as return type & convert the date calculations to text value like: 

=IF(ISBLANK([RequestState]),
	"N/A",
     IF([RequestState]="SUN",
		Text(TODAY()-SUN_Initiated, "dd/mm/yyyy"),
		IF([RequestState]="DS",
			Text(TODAY()-DS_Initiated, "dd/mm/yyyy"),
			IF([RequestState]="TechEffort",
				Text(TODAY()-TechEffort_Initiated, "dd/mm/yyyy"),
				IF([RequestState]="CCB",
					Text(TODAY()-CCB_Initiated, "dd/mm/yyyy"),
					IF([RequestState]="Release",
						Text(TODAY()-Release_Initiated, "dd/mm/yyyy"),
						Text(TODAY()-VR_Initiated, "dd/mm/yyyy")
					)
				)
			)
		)
	)
)

Documentation: Calculated Field Formulas 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#3]

@ganeshsanap Thank you for your suggestion!  The issue is not the return type, however.  I would like a whole number answer for 'how many days have passed' for that formula. 

My issue is when the formula encounters a blank column, it defaults to the '12/31/2001' number and results in a huge number for the end.  I would like to just have the answer of 'N/A', but it doesnt seem to want to pick that up.  Your formula seems to be just like mine, so I wonder why...

[#4]

@davyjones Are you saving data to this list manually or using Power apps (customized form or canvas app)?

If using Power apps, there might be some issue with it saving default value for column as '12/31/2001'.

Try creating a new list in same site for testing and try using formula like: 

=IF(ISBLANK([DateColumn]), "Blank", "With Date")

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#5]

@ganeshsanap I am indeed using a Power App to populate the columns. The issue seems to be that the data was migrated over from another tool, and thus did not have values in each of the '(stage)_initiated' columns. I am thinking that I would have to modify the code to look at EVERY one of those stages and filter out those that are blank, because simply looking to the 'requeststate' column might not work.

As for your current suggestion, I am not following what you are asking to do. can you please reiterate?

thank you!