get middle date of quarter 3

[Rychertate]

Is that possible?
Is there a function to find out if given
"yy-q" that you can retrieve the exact
"mm/dd/yyyy" in the middle of the quarter in Access?

 

[SgtJarrow]

If you know the first and last day of the quarter, you could use:

=DateAdd("d", DateDiff("d", dteStartDate, dteEndDate) / 2, dteStartDate)

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[SgtJarrow]

And this function will give you the answer based on a yy-q input:

' ***************** Start Code *******************


Public Function GetMidSeasonDate(strQuarter As String) As Date

Dim dteStartDate As Date
Dim dteEndDate As Date

Select Case Right(strQuarter, 1)
Case "1"
dteStartDate = DateSerial(CInt(Left(strQuarter, 2)), 1, 1)
dteEndDate = DateSerial(CInt(Left(strQuarter, 2)), 3, 31)
Case "2"
dteStartDate = DateSerial(CInt(Left(strQuarter, 2)), 4, 1)
dteEndDate = DateSerial(CInt(Left(strQuarter, 2)), 6, 30)
Case "3"
dteStartDate = DateSerial(CInt(Left(strQuarter, 2)), 7, 1)
dteEndDate = DateSerial(CInt(Left(strQuarter, 2)), 9, 30)
Case "4"
dteStartDate = DateSerial(CInt(Left(strQuarter, 2)), 10, 1)
dteEndDate = DateSerial(CInt(Left(strQuarter, 2)), 12, 31)
End Select

GetMidSeasonDate = DateAdd("d", DateDiff("d", dteStartDate, dteEndDate) / 2, dteStartDate)

End Function


' ***************** End Code *********************

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[MichaelRed]

Public Function basMidQtr(yy_q As String) As Date

    'Michael Red    12/3/03
    'Given the year and quarter (string input ~ "yy" & ? & "q", where: _
     "yy"  reprresents the 2 digit year and "q" is hte quarter _
     within the year) returns the mid point date of the year and quarter.

    Dim Yr As Integer
    Dim Qtr As Integer
    Dim dtSt As Date
    Dim dtEnd As Date

    Yr = CInt(Left(yy_q, 2))
    Qtr = CInt(Right(yy_q, 1))
    
    dtSt = DateAdd("q", Qtr, "1/1/" & Yr)
    dtEnd = DateAdd("q", 1, dtSt) - 1

    basMidQtr = Format(DateAdd("d", dtSt, DateDiff("d", dtSt, dtEnd) / 2), "Short Date")

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SgtJarrow]

And that, folks, is why we defer to MichaelRed....he is the man!!!!

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[SgtJarrow]

But is that actually correct, MichaelRed??? I am getting some bad dates....

?basMidQtr("03_4&quot
2/15/2004
?basMidQtr("03_3&quot
11/15/2003
?basMidQtr("03_2&quot
8/15/2003
?basMidQtr("03_1&quot
5/16/2003

I think the line:
dtSt = DateAdd("q", Qtr, "1/1/" & Yr)
should read:
dtSt = DateAdd("q", Qtr - 1, "1/1/" & Yr)

as I got correct dates with it.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[JonFer]

MidQtr: DateAdd("d",DateDiff("d",DateSerial([iYear],3*[iQtr]-2,1),DateSerial([iYear],3*[iQtr]+1,0))/2,DateSerial([iYear],3*[iQtr]-2,1))

Given the year and quarter separately you can use the above. If you want to allow the "yy-q" format, replace the iYear and iQtr with Left() and Right() functions on the input parameter.

 

[PaulBricker]

JonFer, I looked for some little relation between the quarter and the first month of the quarter and didn't find it. You get a star because you did. 3*Quarter - 2
Excellent.

Paul

 

[MichaelRed]

Robert,

I thought I had already posted. Thanks for the review and correction. It was (obviously?) break time for me.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places