Find End of Quarter Based on Today's Date

[Weekleyba]

My overall goal is to show on a form, how many days left until the end of the federal quarter based on the current date.

I'm not a programmer and still fairly new to Access.

Example: Today's date is 10 Apr 2017, end of quarter is 30 Jun 2017; therefore, there is 81 days left in the quarter.

How can I write a calculated Control Source to find this?
I won't bore you with my failed solutions...

Any help would be appreciated!

 

[bob fitz]

The DateDiff() function should be able to give you what you want.
Something like:

= datediff("d",date(),#30 jun 2017#)

 

[plog]

For a more general solution (one that works without you hard coding a date) , I think you need to create a custom function and then use that function call as the control source:

=get_QuarterDaysLeft()

Then in a module you build a function to take today's date, calculate the next quarter end date and return the difference. Very roughly (meaning the below will not work) it would look like so:

Function get_QuarterDaysLeft() As Integer
 ' returns number of days left in this quarter

ret =-1      ' return value, by default is -1 to show error

dt_QuarterEnd ="3/31"    ' will hold month/day of quarter end

if DatePart(Date() = 2) then dt_QuarterEnd="6/30"
if DatePart(Date() = 3) then dt_QuarterEnd="9/30"
if DatePart(Date()=4) then dt_QuarterEnd="12/31"
 ' determines current quarter and assigns correct ending month/day

ret= DateDiff("d", DateValue(dt_QuarterEnd & "/" & Year(Date()), Date())
  ' gets difference between quarter end and today

get_QuarterDaysLeft =ret
    ' returns value

End Function

Again, very rough code, you will get errors, but I've gotten you 95% of the way to a general solution.

 

[Weekleyba]

Thanks Bob,
But any idea on how to make it find the last day in the quarter automatically so I don't have to go in and change the date every time a new quarter rolls around?

i.e. changing #30 jun 2017# to #30 Sep 2017# and so on.

 

[Weekleyba]

Thanks Plog. I'll chew on that for a while and see if I can't figure it out.

 

[MarkK]

Use a formula like this to calculate the last day of the current quarter...

=DateAdd("q", Format(Date(), "q"), DateSerial(Year(Date()), 1, 1)) - 1

...which adds the current quarter to the first day of the current year, and subtracts one day.

 

[MarkK]

For days left, just subtract the current date, like...

=DateAdd("q", Format(Date(), "q"), DateSerial(Year(Date()), 1, 1)) - 1[COLOR="Blue"] - Date()[/COLOR]

hth

 

[The_Doc_Man]

Or consider this approach if you don't like IF ladders. Plog: Is the default for DatePart a "q" ?? Because I didn't see anything in your datepart calls to specify WHICH part you were selecting. I thought the default was days if it was anything at all.

Public Function DaysInQ( ThisDate as Date ) as Long

Dim sYear as String
Dim dtEOQ as Date

sYear = CStr( DatePart( "yyyy", ThisDate ) )

Select Case DatePart( "q", ThisDate )
    Case 1
        dtEOQ = "3/31/" & sYear
    Case 2
        dtEOQ = "6/30/" & sYear
    Case 3
        dtEOQ = "9/30/" & sYear
    Case 4
        dtEOQ = "12/31/" & sYear
End Select

DaysInQ = DateDiff( "d", ThisDate, Cdate( dtEOQ ) )

End Function

This should return an integer number of days until the next quarter boundary.

 

[MarkK]

Expressed as a function, you could do...

Function DaysLeftInQ(d1 as date) as Long
[COLOR="Green"]'  add the quarter of d1 to the first of the year of d1, and subtract (d1 + 1) [/COLOR]
   DaysLeftInQ = DateAdd("q", Format(d1, "q"), DateSerial(Year(d1), 1, 1)) - (d1 + 1)
End Function

 

[plog]

Plog: Is the default for DatePart a "q" ??

No, as I said twice, it would throw an error with my exact code. I try to lead horses to water, not bring it to them.

 

[Weekleyba]

Thanks for all for the replies.
There always more than one way to 'skin the cat' isn't there.
(not sure where that saying comes from??)

Anyway thanks to Plog, Mark, and Doc Man for your solutions.
I was able to get Plog's and Mark's to work but not Doc Man's.
Not sure why.
But thanks again guys!

test

 

[Weekleyba]

Hey Plog,
Thanks again. This is how I had to modify to get it to work.
I'm learning.

Function get_QuarterDaysLeft() As Integer
 ' returns number of days left in this quarter
ret = -1     ' return value, by default is -1 to show error
dt_QuarterEnd = "3/31"   ' will hold month/day of quarter end
If DatePart("q", Date) = 2 Then dt_QuarterEnd = "6/30"
If DatePart("q", Date) = 3 Then dt_QuarterEnd = "9/30"
If DatePart("q", Date) = 4 Then dt_QuarterEnd = "12/31"
 ' determines current quarter and assigns correct ending month/day
ret = DateDiff("d", Date, DateValue(dt_QuarterEnd & "/" & Year(Date)))
  ' gets difference between quarter end and today
get_QuarterDaysLeft = ret
   ' returns value
End Function

 

[Weekleyba]

MarkK,
Here's what I ended up putting in the Control Source which gives the same result as Plog's. This may be an easier solution from a non programmer's perspective.
Thank Mark for the help.

=DateDiff("d",Date(),DateAdd("q",Format(Date(),"q"),DateSerial(Year(Date()),1,1))-1)

 

[The_Doc_Man]

There always more than one way to 'skin the cat' isn't there.

Absolutely true.