Previous Topic Next Topic

EXCEL DATE FORMULA (TRICKY)

Closed
Abbas - Jul 18, 2010 at 01:13 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 3, 2010 at 06:00 AM
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 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 18, 2010 at 05:57 PM
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
Abbas
Jul 19, 2010 at 04:13 AM
Sorry I didnt understand a thing.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 19, 2010 at 04:40 AM
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
Abbas
Jul 19, 2010 at 07:35 AM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 19, 2010 at 08:38 AM
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
Abbas
Jul 26, 2010 at 03:39 AM
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
Answer 2 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 18, 2010 at 07:41 AM
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
Abbas
Jul 19, 2010 at 04:12 AM
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

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses
formulas for 1st and 2nd position in class
jack -
suff954 -

1 response
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses