Need a bit of help working out a date?

[PaulSpell]

I need to work out whether the last quarter end is more than, or less than, 8 weeks prior to the current date.

Anyone got any ideas for a quick way to do this?

 

[Newman]

I'm not sure I understand your question correctly...
If "Last Quarter End" is a field, this should do:
=IIf(DateAdd("ww";8;Date())>[LastQuarterEnd];"Less than 8 weeks from now";"More than 8 weeks from now")

 

[PaulSpell]

If only it were that simple. I need to calculate the last quarter end (which is where I'm having a little trouble) and then use that date to establish whether or not 8 weeks has past.

I can do this by writing a select statement with hard coded quarter end dates, and then use another statement to calc the rest. But is there a more straight forward way that I am missing?

 

[Newman]

Sorry, to hard for me. The time I figure it out, you'd probably have received an answer already. Call me back if no one else comes to the rescue.

 

[PaulSpell]

OK thanks anyway.

Any more takers?

 

[simongallop]

Are your quarters variable or 31 Mar, 30 Jun, 30 Sep and 31 Dec? This code is for the normal quarters. Also not too sure what you meant so here I am seeing if the quarter FINISHED more or less than 8 weeks ago.


Sub 8Week_Gap()

QuartDate = Calc_Quart
If DateAdd("ww",-8,Date()) < QuartDate then
MsgBox "Quarter ended less than eight weeks ago"
Else
MsgBox "Quarter ended more than eigth weeks ago"
End If

End Sub

Function Calc_Quart()
tmpDate = DateAdd("m", -3, Date)
Calc_Quart = DateAdd("d", -1, DateAdd("m", 3, DateValue("01/" & (Int((Month(tmpDate) - 1) / 3) * 3) + 1 & "/" & Year(tmpDate))))

End Function

Haven't bothered to see if the calculation can be shorter as have amended the code from something where I get the beginning of the quarter.

HTH

 

[PaulSpell]

Thanks, this looks exactly what I need. I assume this will also work for months (getting the first or last day) as well?

 

[PaulSpell]

Function Calc_Quart()
tmpDate = DateAdd("m", -3, Date)
Calc_Quart = DateAdd("d", -1, DateAdd("m", 3, DateValue("01/" & (Int((Month(tmpDate) - 1) / 3) * 3) + 1 & "/" & Year(tmpDate))))

End Function

I've done some testing on this now and it works great, but I don't know why! Can you explain the logic here please?

 

[simongallop]

The original formula was designed to get the first day of the last complete quarter and it was:

tmpDate = DateAdd("m", -3, Date)
Calc_Quart = DateValue("01/" & (Int((Month(tmpDate) - 1) / 3) * 3) + 1 & "/" & Year(tmpDate))

The logic is: Subtract 3 months from today so that the year bit will always be correct. So it would now be Feb-03

Then: take the month number ie (Feb =2) subtract 1 then divide by 3 and take the integer value. So now we have 0. Multiply by 3 and then add 1 as we cannot have 0 as a month. That gives us January.

So having the start of the quarter being 01-Jan-03 I added 3 months and subtracted one day to get the end of the period ie 31-Mar-03.

The reason for the -1 and +1 bits is that I needed months 1, 2 and 3 to be in Q1 but if I divide 1 by 3 I get int 0 yet 3 / 3 is int 1. I needed them all to be the same number when divided by 3. I could have added 2 instead of subtracting 1 and later adding 1, but there we go. The beauty of coding is that there are many ways of solving the same problem!

 

[PaulSpell]

Thanks for the explanation, I think I need a wet towel to wrap around my head now!!

How on earth did you work that out?

 

[simongallop]

Necessity and boredom!

 

[Newman]

Nice one Harry!
And nicely explained too.
Told you Paul that someone would have come out with the answer before I would... And looking at the formula, he would have been faster than me even if he had took a month to answer.