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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You can use the formula like. In this if the month changes, then it will show 64, else it will use the old formula

=IF(TEXT(E4,"YYYYMM")=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),"YYYYMM"),64,DAYS360($E4,$N$1))
Thanks...this partly solves the problem but leaves an incorrect date in the Age column. I dont want to alter any details except that the invoice amount should move to the next column.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
then dont use age column to decide where data should appear. Calculate the age on fly. One has to be to little creative in resolving issues.
Thanks... I think I am near to solving my problem with ur help. How do I calculate age on the fly?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
by fly I mean to sy that dont rely on age column to see where if to display the data or not. Calculate the age in each cell and then make decision to show or not to show data
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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
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.