Trying to create a status column for rental assets

Dan Kadushin

I’m running into an issue where I’m trying to make a column formula return a status of an asset depending on the current date, and the date it was checked out.

=IF([Assignee Due Date]@row < TODAY(), "OVERDUE", IF(ISDATE([Assignee Check-Out Date]@row), "Checked-Out", IF(ISBLANK([Asset Name]@row), " ", "Checked-In")))

Basically, I wanted to return a blank if there was nothing in the Asset Name column, a “Checked-In” status if there was an asset listed in the Asset Name column, a “Checked-

Out” status if there was a date in the “Assignee Check-Out Date” column, and an “Overdue” status if the due date column was past the current date. All the date columns are set as dates, but I keep getting an #INVALID OPERATION error in the formula column for rows with ONLY something entered in the Asset Name row.

If I enter a date into the Assignee Check-Out Date, then a "Checked Out" status appears, and if I set it far enough back that the Assignee Due Date (that column set to =IF(ISBLANK([Assignee Check-Out Date]@row), " ", WORKDAY([Assignee Check-Out Date]@row, 7)) ) is in the past an "OVERDUE" status appears.

Been trying different iterations of this as well:

=IF([Assignee Due Date]@row < TODAY(), "OVERDUE", IF(AND(NOT(ISBLANK([Asset Name]@row))),ISDATE([Assignee Check-Out Date]@row))), "Checked Out", "Checked In") 

But I couldn't figure out how to include the blank status with this syntax.

Sameer Karkhanis

@Dan Kadushin Based on requirement/logic you mentioned you wanted "Checked-Out" status if the Assignees Check-Out Date exists --- There is/was no other condition mentioned and so my previous formula will always get that status.

Yes, you are getting the INVALID OPERATION as the "Assignee Due Date" column value is formula based depending on "Assignee Check-Out Date", but that is easy to solve for, see below,

=IF(ISBLANK([Asset Name]@row), "", IF(ISDATE([Assignee Check-Out Date]@row), IF([Assignee Due Date]@row < TODAY(), "OverDue", "Checked-Out"), "Checked-In"))

Hope this helps.

Andrée Starå

Hi @Dan Kadushin

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Have a fantastic day & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Dan Kadushin

Hi @Andrée Starå

I attached a screen shot to my original post, but my organization won't let me share workspaces. Did the screen shot not show up? I'll reattach it to this post.

Asset Tracker.JPG

This is the formula currently in Asset Status column in that screenshot:

=IF([Assignee Due Date]@row < TODAY(), "OVERDUE", IF(ISDATE([Assignee Check-Out Date]@row), "Checked-Out", IF(ISBLANK([Asset Name]@row), " ", "Checked-In")))

The Overdue and Checked Out parts of the formula seem to be working, and I have a feeling the #INVALID OPERATION issue might have to do with using the TODAY in the first part of the formula? Is the ISBLANK function at the end trying to compare to a date to a text string because of the TODAY and causing the error?

Thanks!

Paul Newcome

Try setting the IF/ISBLANK as the first argument.

Dan Kadushin

Hi @Paul Newcome, I'm a little confused about how to nest the other parts of the formula if I make that the first argument.

=IF(ISBLANK([Asset Name]@row), " ", IF([Assignee Due Date]@row < TODAY(), "OVERDUE", IF(ISDATE([Assignee Check-Out Date]@row), "Checked-Out"), "Checked-In")) ???

That's giving me an #INCORRECT ARGUEMENT SET error.

Paul Newcome

Try removing the closing parenthesis after "Checked-Out".

Sameer Karkhanis

@Dan Kadushin

If you build your logic by checking the existence of asset name first then you can get the expression to do what you are looking for. Try the below,

Assumption: You don't care if the checkout date is pre or post assignee due date and only care if it exists or not

=IF(ISBLANK([Asset Name]@row), "", IF(ISDATE([Assignee Check-Out Date]@row), "Checked-Out", IF([Assignee Due Date]@row < TODAY(), "OverDue", "Checked-In")))

Dan Kadushin

@Paul Newcome and @SK, I'm getting #INVALID OPERATION errors for both of those formulas when the Assignee Check-Out Date column is blank in that row. Paul, it returns the correct status when I fill out a date in that column. SK, your formula was returning "Checked-Out" regardless of whether the date in the Assignee Due Date column was before or after today.

The Assignee Due Date column is itself another formula based on the Assignee Check-Out Date column. =IF(ISBLANK([Assignee Check-Out Date]@row), " ", WORKDAY([Assignee Check-Out Date]@row, 7))

This sets an Assignee Due Date seven working days after the date in the Assignee Check-Out Date column on that row. Is that what's causing the #IO issue?

Sameer Karkhanis

@Dan Kadushin Based on requirement/logic you mentioned you wanted "Checked-Out" status if the Assignees Check-Out Date exists --- There is/was no other condition mentioned and so my previous formula will always get that status.

Yes, you are getting the INVALID OPERATION as the "Assignee Due Date" column value is formula based depending on "Assignee Check-Out Date", but that is easy to solve for, see below,

=IF(ISBLANK([Asset Name]@row), "", IF(ISDATE([Assignee Check-Out Date]@row), IF([Assignee Due Date]@row < TODAY(), "OverDue", "Checked-Out"), "Checked-In"))

Hope this helps.

Dan Kadushin

@SK

That did it! Thank you so much! This community is the greatest, thank you all for your suggestions and help!

Sameer Karkhanis

@Dan Kadushin Glad it worked for you.

Andrée Starå

@Dan Kadushin

Happy to help!

I saw that it got answered already!

Let me know if I can help with anything else!

Best,

Andrée