forumla that takes into account the year to recognizes the year

Hi all,

need to your help so thank you in advanced!

Context;-

i download circa. 40,000 lines worth of data for revenue (columns A to N) with most of the data comes up with a specific period per service stated in the item description field (column I).

Using that date i try to work the value i need to release into the month (column X), the value i need to defer (column W) and what element of what is released relates to a prior period (column Z).

From column P to V i've created some formulas to pick up the relevant data and separated the start month, end month, start year, end year. Then I've created some formula using IF to calculate the deferred, released and back-dated amount. The current formulas work great if all the data is in the same year however it falls down when the data relates to a different year.

i think the current formula just needs to incorporate the year in it somehow but im struggling as going up my own proverbial!

any help would be great!

Instead of breaking a date down into a month number and a two digit year as in W1 & Z1, just enter any daye in the month you're interested in. e.g. put 1/11/2018 in U1

Then W2 becomes

Formula:

Please Login or Register  to view this content.

and in Z2

Formula:

Please Login or Register  to view this content.

Formulas in columns Q and R derive the date in text format but it should be in date format.
Try this formulas
In Q3 then copy down

=IFERROR(DATEVALUE(LEFT(P3,8)),"")

In R3 then copy down

=IFERROR(DATEVALUE(RIGHT(P3,8)),"")


In S3 then copy down up to column T

=IFERROR(MONTH(Q3),"")

In U3 then copy down up to column V

=IFERROR(YEAR(Q3),"")

The formula I gave you will only 'break down' if the date I suggested in say U1 is before the end date in column R.
I have assumed that you are wanting a multiple of the number of months between some start date that you define, (currently you use a month number and a two digit year number) and the column R end date.

If so please advise what you want to see if the start date is prior to the end date.
Trivially you could wrap the formula I gave you in an =IFERROR() function to show a blank

In W3 then copy down

=IF(S3>$W$1,G3,IF(T3+12*(V3-(2000+$Z$1))<$W$1,0,((T3-$W$1)+12*(V3-(2000+$Z$1)))*(J3*K3)))

Year selected is 18 in AA1 but the year in Row 9 is 19.Even then does it treated as part of the month?
Also I feel if the date in column R is not the end day of the month in X1 irrespective of the year , it should take the invoice amount in Column G.Is this correct.

Just a bit of advice, can I suggest that when wanting to manipulate data that relies on dates youj include a proper date number rather than just two digits from the end of the year. It's more difficult to build a date from that than to use a function to extract the year number digits from a date.

Sorry for the delay. I am not able to understand clearly what is required.It is better to use dates in columns Q and R rather than the months and dates.
KVS