A challenge for you... End of the month plus more!

[slay0r]

Hey guys..this is a bit complicated so bear with me but it should be a suitable challenge for you experts!

I basically need to know what will be outstanding at the end of the month that is OVER 45 days (at the end of the month though..not now!) The issue is that the end of month command isn't sufficient because it might fall on a weekend so I can't say it's the 1st of this month or the 2nd of this month because it's not on a set day, it's on the first working day of the month so for example the next 3 months it will be 1st November, 3rd December, 2nd January. Is there any code I can use to do this?

I need it to delete anything that isn't within the above range so then I can manipulate the data as I plan to. From the below example the top line would be deleted as by the 1st of November which is the first working day of the month it will be only 25 days old so therefore not over 45 days at the end of the month (or beginning of the next month if that command is easier?!) The second line will be included because although it's only 41 days old today on the 24th October 2012, it will be 49 days old by the time it hits 1st November. Hope that makes sense

Any help would be incredibly appreciated, I'm stumped on this one..

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Company
[/TD]
[TD]Alpha
[/TD]
[TD]Long
[/TD]
[TD]Invoice Date
[/TD]
[TD]Invoice Age
[/TD]
[TD]Total O/S
[/TD]
[TD]CC Section
[/TD]
[/TR]
[TR]
[TD]130
[/TD]
[TD]Test1
[/TD]
[TD]Testlonger1
[/TD]
[TD]08/10/12
[/TD]
[TD]17
[/TD]
[TD]1231.54
[/TD]
[TD]Tom
[/TD]
[/TR]
[TR]
[TD]147
[/TD]
[TD]Test2
[/TD]
[TD]Testlonger2
[/TD]
[TD]14/09/12
[/TD]
[TD]41
[/TD]
[TD]3527.23
[/TD]
[TD]Laura
[/TD]
[/TR]
[TR]
[TD]147
[/TD]
[TD]Test3
[/TD]
[TD]Testlonger3
[/TD]
[TD]11/11/11
[/TD]
[TD]349
[/TD]
[TD]129.42
[/TD]
[TD]Tom
[/TD]
[/TR]
[TR]
[TD]130
[/TD]
[TD]Test4
[/TD]
[TD]Testlonger4
[/TD]
[TD]03/05/12
[/TD]
[TD]175
[/TD]
[TD]1532.98
[/TD]
[TD]Steph
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[MisterBates]

Not the shortest formula in the world, but this returns the first weekday (assuming weekdays are Mon-Fri) of next month:

=MIN(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,1),2)<6,DATE(YEAR(NOW()),MONTH(NOW())+1,1),NOW()+360),IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,2),2)<6,DATE(YEAR(NOW()),MONTH(NOW())+1,2),NOW()+360),IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,3),2)<6,DATE(YEAR(NOW()),MONTH(NOW())+1,3),NOW()+360))

This works by:
* Using DATE() and NOW() to get the first day of next month
* Using WEEKDAY(,2) to get the weekday number (1=Mon, 7=Sun)
* Using IF() to test the weekday and if Mon-Fri, return the date, else return a date 360 days from now
* Using MIN() to choose the earliest date, with three similar formulae to test the 1st, 2nd and 3rd of next month

By replacing now() with a reference to a cell (e.g. cell A1), you can enter any date in the cell and get the first weekday of the month following that date:

=MIN(IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),2)<6,DATE(YEAR(A1),MONTH(A1)+1,1),A1+360),IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2),2)<6,DATE(YEAR(A1),MONTH(A1)+1,2),A1+360),IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,3),2)<6,DATE(YEAR(A1),MONTH(A1)+1,3),A1+360))

You could use this by adding another column calculating the age as at first working day of next month and filtering on those rows with the 45 days or more result

 

[Andrew Poulsom]

How about?

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1,1)

 

[martindwilson]

first workday of any month is
=WORKDAY(EOMONTH(A1,-1), 1,D1:D10) where d1:d10 is the holidays

 

[slay0r]

Guys that's perfect apart from one small thing, if the date is last year it does it to last years month so for example 10/10/11 comes out as 03/11/11 rather than this month, is there a simple fix for that?

 

[Andrew Poulsom]

Why would you want the formula to assume that 10/10/11 means this month?

 

[slay0r]

The reason is because I want to calculate the difference between the invoice date and the first working day of next month (I thought it was going to be more complicated than this but as it works out it can be this simple!)

E.g I want to use the Invoice date from the example above, then use the first working day from next month so in this case ONLY November to work out how many days there are from invoice date to the end of THIS month (well the beginning of next month) , a working example of what I'm trying to do below:

Invoice date: EOM DATE DAYS:

10/09/2012 01/10/2012 21
10/10/2012 01/11/2012 22

If you see what I mean from that, the top line in red should actually be 52 days to the beginning of next month which means it will be over the 45 day threshold I want to use as a filter, what I need is for the formula to use the beginning of next month from the current month not from the cell month. I'm even confusing myself here! Hope that helps a bit..

 

[Andrew Poulsom]

Oh I see:

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,1)

 

[slay0r]

You sir, are a massive legend. Thanks to the other guys for their help as well, it's always appreciated!