Dear all,
I am using the following formula to check the overdue date vs Today date.
Can anyone share your advice on how to shorten it.
=IF(OR(ISNUMBER(SEARCH("04",X10)),ISNUMBER(SEARCH("05",X10)),ISNUMBER(SEARCH("13",X10)),ISNUMBER(SEARCH("11",X10)),ISNUMBER(SEARCH("12",X10))),"",TODAY()-F10-SUMPRODUCT((Holiday>=F10)*(Holiday<=TODAY())))
Thank
Pjlau
Can anyone help on that?
It will be clearer if you upload sample workbook and explain in more detail the specific requirements.
However, if the goal is to simply compare the overdue date to the current date, then will work for you?
=IF(TODAY()>X10,"overdue","")
Or take a look at using the DATEDIF function
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Thx. I have attached a sample workbook for ease of reference.
Column C is to calculate the Overdue date.
When input the status code e.g. 11,12,13, 04,05 in column X, the overdue date will be blank.
Perhaps make a small table with your statuscodes ( call it status_code) and enter =IF(countif(status_code,X10),"",(?????))
Not sure about the last part Are you including weekends or not in your count?
thx. Actually, I would like to exclude the weekend i.e. Sat and Sun and a Holdiay table but I don't know how to define it. Can u help on that?
In that case =IF(countif(status_code,X10),"",NETWORKDAYS(F10,today(),Holiday))
Great. Thx for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks