I'm trying to use a nested IF formula to disaggreggate transactions into distinct aging buckets. I'm getting an error (#NAME) with the following:
=IF(N2<5/27/2017,"over 90",IF(N2<6/27/2017,"60 to 90",IF(N2<7/27/2017,"30 to 60","current")))
N2 represents the transaction posting date; can anyone advise on what's wrong with my formula?
Try "hashing" your dates:
Formula:
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Hi mjl and welcome to the forum,
Try this formula which will move with each new day.
Please Login or Register to view this content.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
@Orrin,
Hashing the dates didn't seem to help. I remember something about hashing dates.. It didn't work for this problem.... Was it dates in Advanced Filters where we needed to hash those dates?
@ Marvin - I don't remember - it's probably just in VBA and not formulas
Maybe...
=IF(N2 < DATEVALUE("5/27/2017"),"over 90",IF(N2 < DATEVALUE("6/27/2017"),"60 to 90",IF(N2 < DATEVALUE("7/27/2017"),"30 to 60","current")))
HTH
Regards, Jeff
Please Login or Register to view this content.
Last edited by protonLeah; 09-02-2017 at 05:11 PM.
Ben Van Johnson
Thanks for the replies. Unfortunately both the hashtags and "DATEVALUE" options didn't resolve the problem. Also, I need the formula to be static and not change with each day. Any other suggestions?
Yes, upload a sample workbook.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Hi,
Simply replace the "Now()" with the dates you want. BUTthose dates will need to be understood by Excel as Numbers and NOT Text.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks