PDA

View Full Version : Solved: month end reminder



ndendrinos
07-08-2007, 04:26 PM
Hello ,

I need a reminder that will show (on open) a MsgBox from the 27th to (and including) the 31st of each month. (that if possible could take care of Feb and all 30 days months)
Thank you

JimmyTheHand
07-08-2007, 10:10 PM
Private Sub Workbook_Open()
If Day(Date) >= 27 Then MsgBox "Month end is near."
End Sub

ndendrinos
07-09-2007, 02:50 AM
Thank you JimmyTheHand
Will then this work?
Private Sub Workbook_Open()
If Day(Date) >= 27 Or =31 Then MsgBox "Month end is near."
End Sub

Bob Phillips
07-09-2007, 03:19 AM
31 is >= 27, so it is totally superfluous.

ndendrinos
07-09-2007, 03:46 AM
Yes of course it is.
Thank you

mdmackillop
07-09-2007, 05:42 AM
31 is >= 27, so it is totally superfluous.
As is "totally" :devil2:

unmarkedhelicopter
07-09-2007, 07:07 AM
it is also very difficult for "=31" to be true ...

ndendrinos
07-09-2007, 09:24 AM
I posted my first reply at 5AM . At 6AM while on my way to work I figured I was going to get it . Found MD's observation amusing .
Now to the helicopter : not sure I understand but assuming I would change my request and have the code run from the 27th to the 29th inclusive would one write it like this then ?



Private Sub Workbook_Open()
If Day(Date) >= 27 Or =<30 Then MsgBox "Month end is near."
End Sub

OdiN
07-09-2007, 10:44 AM
I posted my first reply at 5AM . At 6AM while on my way to work I figured I was going to get it . Found MD's observation amusing .
Now to the helicopter : not sure I understand but assuming I would change my request and have the code run from the 27th to the 29th inclusive would one write it like this then ?



Private Sub Workbook_Open()
If Day(Date) >= 27 Or =<30 Then MsgBox "Month end is near."
End Sub

That would generate a message box on every day less than 30 and also on dates that are 31. Though you would write <=30 not =<30.

To display a warning on every day from 27-29 write this:

If Day(Date) >= 27 AND <=29 Then MsgBox "Month end is near."

ndendrinos
07-09-2007, 10:55 AM
Thank you OdiN all noted
Regards to all
Nick

Wolfgang
07-09-2007, 12:27 PM
Hi Bob,

Do you happen to know which version of Microsoft Excel Cicero was using to come up with a statement like that?!

Best,
Wolfgang

ndendrinos
07-09-2007, 01:54 PM
If Day(Date) >= 27 AND <=29 Then MsgBox "Month end is near."
Since all of you are in a great mood maybe you can teach me this:
I read above as: If today's date is equal or bigger that the 27th and smaller or equal to the 29th then ...
Why can't I express it like this then ? (just curious)

If Day(Date) >26 Or <30 Then MsgBox "Month end is near."

mdmackillop
07-09-2007, 02:10 PM
In the second case any numver from 1 - 29 satisfies the criteria of being < 30. Similarly 17 - 31 are >26, so you'll get messages for everything.
Try the following on a worksheet
=IF(OR(F5>26,F5<30),TRUE,FALSE)
and
=IF(AND(F5>=26,F5<=30),TRUE,FALSE)

OdiN
07-09-2007, 02:21 PM
Okay you don't seem to be getting the logic of the OR/AND statements so I will attempt to explain.

For an OR statement you have this:

If (Condition 1) Or (Condition 2) Then

Do Code

End If

If either Condition 1 OR Condition 2 are true, the Do Code will run. So in your case you have this:

If Date > 26 Or Date < 30

All dates from 1-31 are either going to be > 26 or < 30, so the code will always run.


For an AND statement you have this:

If (Condition 1) AND (Condition 2) Then

Do Code

End If

Now this time in order for the Do Code to run BOTH Condition 1 AND Condition 2 must be true. In the OR example, only one of the conditions must be true in order for the Do Code to run.

So the statement If Date > 26 And Date < 30

That means that the Date must be between 26 and 30 for the conditions to both be true and the Do Code to run. 26 !> 26 and 30 !<30 so the code will not run if the date is the 26th or 30th.

In both cases, OR and AND - you can have more than 2 conditions, but it can get complex. You can also combine conditions. For Example:

If Date >=20 AND Date <=31 OR Date = 1 Then

Do Code

Else

Do Other Code

End If

Also you can do multiple conditions:

If (Condition 1) And (Condition 2) And (Condition 3) And (Condition 4) Then


Etc. etc.


I hope this makes things more clear.

ndendrinos
07-09-2007, 02:31 PM
Sorry MD I ment it like this:

If Day(Date) >26 And <30 Then MsgBox "Month end is near."

Bob Phillips
07-09-2007, 02:36 PM
Hi Bob,

Do you happen to know which version of Microsoft Excel Cicero was using to come up with a statement like that?!

Best,
Wolfgang

Of course! I.VCI.XXVII, but it never took off, couldn't get the date function to work.

OdiN
07-09-2007, 02:36 PM
Sorry MD I ment it like this:

If Day(Date) >26 And <30 Then MsgBox "Month end is near."

That would work and it would run on 27, 28, and 29.

However, it's not easy to just look at it quickly and know when the conditions are met.

With >= 27 AND <=29 you can see right off the bat what date range you're looking for. It's the same thing really, just depends on if you're a Type A or Type B person I guess :P

ndendrinos
07-09-2007, 02:43 PM
OdiN thank you for the explanation it's good of you to have taken the time.
So if I understand you my concept is correct but badly writen

If Day(Date) >26 And <30 Then MsgBox "Month end is near."
Here it means that if the date is the 27th . 28th or the 29th then the code runs.

Bob Phillips
07-09-2007, 02:47 PM
If Day(Date) > 26 And _
Day(Date) < Day(DateSerial(Year(Date), Month(Date) + 1, 0) - 1) Then
MsgBox "Month end is near."
End If

OdiN
07-09-2007, 02:48 PM
I wouldn't say it's badly written, but I personally just wouldn't write it that way. If that works for you, then there you go :)

mdmackillop
07-09-2007, 02:49 PM
A slight twist.
Get the message from a set number of working days before the month end. (needs the Analysis Toolpak Add-in installed)

Private Sub Workbook_Open()
If Application.Evaluate("=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0))") < 3 Then
MsgBox "The end is nigh!"
End If
End Sub

OdiN
07-09-2007, 02:49 PM
If Day(Date) > 26 And _
Day(Date) < Day(DateSerial(Year(Date), Month(Date) + 1, 0) - 1) Then
MsgBox "Month end is near."
End If


You just had to find a more complicated way? :P

Bob Phillips
07-09-2007, 02:51 PM
OdiN thank you for the explanation it's good of you to have taken the time.
So if I understand you my concept is correct but badly writen

If Day(Date) >26 And <30 Then MsgBox "Month end is near."
Here it means that if the date is the 27th . 28th or the 29th then the code runs.

No it doesn't, it is syntactically incorrect.

Bob Phillips
07-09-2007, 02:52 PM
No it doesn't, it is syntactically incorrect.

No, it is different, and variable, depending upon the month.

ndendrinos
07-09-2007, 02:54 PM
It's the same thing really, just depends on if you're a Type A or Type B person I guess :P
Thank you once more I was typing my last post ... as for your quote I will have you know that when in this forum I'm a Type H (hopeless) :biglaugh:
For the past three years I atone for past sins by visiting and I'm almost done.
Big thanks to all of you

mdmackillop
07-09-2007, 02:54 PM
Of course! I.VCI.XXVII, but it never took off, couldn't get the date function to work.
I believe the main problem was with the BC date evaluation. Nobody could agree on Day 1, and the American date format just made things worse.

Bob Phillips
07-09-2007, 03:07 PM
I believe the main problem was with the BC date evaluation. Nobody could agree on Day 1, and the American date format just made things worse.

I think you are right ... and as ever the Yanks mess it up <g>

ndendrinos
07-09-2007, 03:16 PM
Would you all mind if I continue watching this creation of mine (my post) and see where it leads to ? I promise not to interfere.

ndendrinos
07-09-2007, 03:29 PM
I know I said I was leaving ... but I have to ask this:

Private Sub Workbook_Open()
If Application.Evaluate("=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0))") < 3 Then
MsgBox "The end is nigh!"
End If
End Sub
what happens around the end of December when we shut down on the 28th and resume on the second of January ?
MD I read ypur code as : if the net working days between today and the end of the month are less than 3 then run the code. Correct me if I'm wrong

mdmackillop
07-09-2007, 03:37 PM
That is correct. You can adjust for December quite simply
Private Sub Workbook_Open()
Dim EndDays As Long
If Month(Date) = 12 Then
EndDays = 6
Else
EndDays = 3
End If
If Application.Evaluate("=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0))") < EndDays Then
MsgBox "The end is nigh!"
End If
End Sub

ndendrinos
07-09-2007, 04:58 PM
Momentous occasion I finally got something right & will quit while I'm ahead.
Thank you all again