EXCEL DATE FORMULA (TRICKY)

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I am facing a typical problem for which I need a workaround in Excel. Our financial system (legacy software) gives us a report of our total payment out standings based on the invoices generated on daily basis. the same is divided as 0-30 days, 31-60 days etc. I also manage the same in Excel to generate reports and charts for management purposes. The problem arises in the way our financial system moves the invoices to the age groups that I mentioned above. Even if the invoice is generated on the end of month and should fall in the 0-30 days category, if the month changes it automatically skips it to the next month. E.g. inv no. 2341 generated on 20-June 2010 shows in the 0-30 age group till 30th June 2010; If I take the report on July 1st I see it moved to the 31-60 days category. I want the same thing to be replicated in Excel. Currently I am using this formula to check on the age of the invoice and move it to the various age groups automatically [=IF(AND($L257<=30),$F257*1,"0")] /// [=IF(AND($L257<=60,$L257>30),$F257*1,"0")] .... Where L is the column where I calculate the age of invoice in days. Can you help me with a formula in Excel to also check if month has changed and then move the invoice amount automatically to the next age group.

I hope you can help me out.

Regards,

Abbas Dehnuwala


2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am not sure about if dates are in future or past etc
How ever you can use date manipulation to see if this date fall in other month. Some thing like this


IF(TEXT(A1,"YYYYMM")=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),"YYYYMM")) "A1 month is one month prior to this month", "A1 month is NOT one month prior to this month")
3
Sorry I didnt understand a thing.
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
From what I understood from your question, the issue is when the day elapsed is less than 30 days and month have changed. I dont know if the dates that are printed out are future days or past days ( based on today's date etc) , I am not able to give you a good answer. What I gave you was just a formula in which I tried to show you how you can check if for a given date, the month is a month prior to current month. In other word today is July 19,2010. So if the given date is some where in June 2010, it will say that "A1 month is one month prior to this month". If that is not the case it will say "A1 month is NOT one month prior to this month". Here "this" refers to calender month (july). Hope it clarifies things
0
The system is designed in such a way that even if the invoice falls with I 0-30 days, if the month changes it moves to the next age group i.e. 31-60 days.
This does not happen for other age groups only for the 0-30 days age group. Hope u can help.
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Have you tried to see what the formula that I gave you does does ?

This is marking for you the dates where month have changed and date elaspsed betweeen todays date and the invoice date is less than 30. You just have to incorporate into your formula this logic
0
Dear Riz,

I am using the following formula to shift the invoices to diff age groups
0-30 days
=IF(AND($L9<=30),$F9*1,"0")
where L counts number of days since the invoice was generated.
31-60 days
=IF(AND($L9<=60,$L9>30),$F9*1,"0")
etc

can u help how to amend this formula to ur solution.
0
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
if invoice date is 20th June 2010 and on 30th June number of days elapsed is 10 days. on July 1st it is only 11 days how can that be in the interval 31-60 days.
This argument of mine shows that I have NOT understood you. please clarify
0
Hi Venkat... Thats the problem I am facing. The system is designed in such a way that even if the invoice falls with is 0-30 days, if the month changes it moves to the next age group i.e. 31-60 days.
This does not happen for other age groups only for the 0-30 days age group. Hope u can help.
0