Lord Help Me!

Matt_at_Loves

Would appreciate some help with a formula!

I ultimately need a column that either shows a count based on the difference between two dates, or a "0."

In one column, we indicate whether a job post is open. "Yes" it's open, "No" it's closed. In another column, we show the date the job was posted. In another column, we show the date an offer was made to a candidate.

I need a fourth column (called "TF") that, if there is a date in the offer column, we show the count based on the time that has passed since the job was posted and an offer was made. If there is no date in the offer column, then we show the count between when the job was posted and today's date. Finally, if the job is no longer posted ("No") and there is no date in the offer column, the count changes to "0."

I feel like I am so close, yet so far away.

COLUMN TITLES

• Column indicating the job is posted: 🚦
• Column indicating date job was opened: 📅🏁
• Column indicating date an offer was made: 📅 OFR
• Column showing count or "0": "TF"

I'm using the formula below, which satisfies the count between the date the job was posted and when an offer was made (or today's date if no offer date):

=IF([📅 OFR]@row <> "", [📅 OFR]@row, TODAY()) - 📅🏁@row

I just need to now add a way to show "0" if the job is closed AND no offer was made.

Again, I appreciate any help!

Cory Page

You should be able to add another if in front of the first if and say if

if(column X = Closed then "0",IF([📅 OFR]@row <> "", [📅 OFR]@row, TODAY()) - 📅🏁@row))

in the above example if the cell is marked as closed the if statement will simply end with a "0" if its not then it will proceed with the rest of the items you already have working. Maybe this will help, I can build a sheet and send proper screenshots and formulas if you need.

Matt_at_Loves

So if I use this formula, and "X = closed," but there is a date in the offer column, then it will still say the count and not "0"?

And THANK YOU, btw.

Cory Page

Yea, because if the first If statement is satisfied the rest are ignored. I use this double if statement for overriding things all the time pretty useful for sure. If its not closed then the first statement is false and it moves on, you can always add another if if the first one is false.. nested if's I do believe they call it.

Matt_at_Loves

I'm trying this...

=IF([🚦]@row = "No", "0"), IF([📅 OFR]@row <> "", [📅 OFR]@row, TODAY()) - 📅🏁@row))

...and getting "column formula syntax" error.

"🚦" is the column that indicates whether the req is open or not. It's a Symbols type column, with the green, yellow, and red symbols (green circle, yellow triangle, red octagon.)

Cory Page

Sorry about that Matt after digging in a bit more to your post and setting up the data it turned out to be a bit more iffy than I originally though.. Here you go take a look and let me know if I missed anything. Hope this helps.

Update: Had some free time to clean up the example for you this should align a little better than my previous post.

=IF(AND([Job is posted]@row = "No", [Offer was made]@row = ""), "0", IF([Offer was made]@row <> "", [Offer was made]@row - [Job was opened]@row, IF([Offer was made]@row = "", TODAY() - [Job was opened]@row)))

Matt_at_Loves

Cory, thank you again for the time you've already spent. I feel like we're almost there.

This is how the formula look swith the column titles I have put in:

=IF(AND([🚦]@row = "No", [📅 OFR]@row = ""), "0", IF([📅 OFR]@row <> "", [🚦]@row - [📅 OFR]@row, IF([📅 OFR]@row = "", [🚦]@row - TODAY())))

And it's returing #INVALID OPERATION

Thank you again. 🙏

Cory Page

@Matt_at_Loves isn't  [🚦]@row column a yes, no, hold.

The below items would require a date to sum? Just curious

[🚦]@row - [📅 OFR]@row

Matt_at_Loves

I think I understand what you're asking. Yeah, that "🚦" column is just a "yes" or "no," so there isn't a date to be subtracting from...

Matt_at_Loves

Maybe the below screenshot is more helpful:

That highlighted one should be "0."

Cory Page

@Matt_at_Loves I used the Job was opened date in the formula's above as it seemed like that was the column you wanted to use as the base date. if you change the yes no column in your formula to the Job opened that should fix it

[🚦]@row - TODAY()

[🚦]@row - [📅 OFR]@row

• Change to: Column indicating date job was opened: 📅🏁

If you change [🚦]@row in the two above lines to the job was opened: 📅🏁 column that should help.

Matt_at_Loves

Cory, this is going to work. The only thing is that it changed all the day counts to negative. I'm sure this is an obvious fix, trying to figure it out now.

Cory Page

@Matt_at_Loves Yea, you can fix that by swapping the date - date 2 columns around.

I noticed you swapped them wasn't sure if you preferred - values or not. Examples below.

TODAY() -job was opened: 📅🏁

[📅 OFR]@row - job was opened: 📅🏁

Matt_at_Loves

Cory! That's it. It's done. You did it:

=IF(AND(🚦@row = "No", [📅 OFR]@row = ""), "0", IF([📅 OFR]@row <> "", [📅 OFR]@row - 📅🏁@row, IF([📅 OFR]@row = "", TODAY() - 📅🏁@row)))

Thank you SO much! Wish I could give you give you... SmartSheet GOLD or something! 🙏🙏🙏

Cory Page

@Matt_at_Loves No worries, I actually like these little challenges they are fun to work on so consider your payment giving me a fun little challenge to think about. I am running out of things to improve in my sheets had to start finding others to keep my skills growing. :)

Matt_at_Loves

Well again, I'm super appreciative, and I'm sure my team is as well! Maybe I'll have another one for you in the future!