Quarter End date: Enter as Short Text or Date/Time? And how to Validate?

dswift

New Member
Joined
Oct 24, 2017
Messages
21
I would like to enter a column in a table where the data should look like a year and a quarter end. For example, I would like to see 2018 Q1 or 2018 Q2...etc. Or I am okay with using actual months but then it must only be quarter end dates (3, 6, 9, 12).

1) Is it best to use a date/time or short text data type?
2) what is the best way to restrict entry so that any (current or future) year can be entered, but restrict the dates to either quarter end (so, 3/31, 6/30, 9/30 or 12/31) or Q1, Q2, Q3 Q4?

Right now I am using short text with an input mask = "201"0" Q"0. However this would allow the input of any number after Q, and it should really only allow 1, 2 3 or 4. When I try to input a validation rule here I have tried #### Q1 or #### Q2 or #### Q3 or #### Q4. I get an error message "The expression you entered has an invalid date value" even though I am using short text and not the date/time data type.

Thank you!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
hello

How about if there is another worksheet with a dedicated table in successive cells: 2018 Q1, 2018 Q2, 2018 Q3, 2018 Q4, 2019 Q1, etc

Give it a defined name, like 'tblQuarterEnding', and use that as data validation on the cells?

regards
 
Upvote 0
Hmm, that could work too. It would probably work better than what I have now. The only downside being that I would have to continuously update it each quarter to add new dates
 
Upvote 0
In general I would find it highly preferable to use real dates. If Q1 ends on 4/30 then use 4/30/yyyy. This allows you to do true date comparisons on date values throughout your database.

You could however still store quarter and year but in that case I'd want to have them as integers and stored in two data point - fiscal year, fiscal quarter. Now you can do yearly reporting, year over year comparisons, quarter over quarter comparisons, easily create true date values from the year and quarter, and probably more good things as well. This is what I've seen in the two enterprise financial databases that I've used in work environments (they stored financial transactions with a year, quarter, and transaction date (at least - sometimes other dates (such as invoice dates or received dates). That way there was always a single "true value" about what quarter a transaction is reported in, regardless of other dates involved. Often there would also be a monthly close routine that switches the system to the new quarter.

As far as having to continuously update the quarters, it isn't uncommon in financial databases to have yearly "maintenance" required for fiscal years. This is mainly because companies have different fiscal calendars and so no financial system can assume that Q1 is Jan-Mar, Q2 is Apr-Jun, etc.

That said, you should be easily able to create such data quite a few years ahead in advance. I like to use Excel to do so when i need to generate lots of data points, since a few formulas do the trick in seconds. Then import to Access.
 
Last edited:
Upvote 0
So, are you suggesting a separate table with two columns: Year (YYYY) and Quarter (Q1, Q2, Q3, Q4). Then pulling in both of those columns into the original table that I was trying to make?
 
Upvote 0
That would be my preference, but not the only way to go.
If you did store string data, it will also work if the data is stored consistent:
2014-Q1
2015-Q2
2018-Q1
etc.

Or
2014-01
2015-02
2018-01

Or something to that effect (which is also pretty easy to parse out as a true date if necessary).

This is sortable and comparable in the expected order (year, then quarter).
In that case the only problem was the proper validation you were trying to find earlier.

As used to be said, there are many roads to Rome.
 
Last edited:
Upvote 0
Note that here would be the original validation rule you were looking for, I believe (4 numbers, a space, a "Q" and finally a number from 1-4.

Code:
Like "[0-9][0-9][0-9][0-9] Q[1-4]"
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top