Create date from quarter / year

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
149
I am having an issue with how some people entered their work. When they closed the tasks they closed it as the year it was closed and the quarter it closed in and not a date of close. Can I create a date of close by taking the Quarter and the year and combining them to show something like this?
Quarter 1
Year 2018
1/1/2018

Quarter 2
Year 2018
4/1/2018

etc......This is quarterly report so I am not worried about exact Months.

Thanks
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hello,

In order to get the first day of the quarter you can test following :

Code:
[COLOR=#333333]=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+1,1)[/COLOR]

Hope this will help
 
Upvote 0
What exactly do they enter/save? Is that two fields with integers (i.e., Year: 2018, Quarter: 2)
 
Upvote 0
The table fields are not integers, they are numbers. Year will be like 2018 and Quarter is like 2. I tried creating a date field but my date picker does not recognize the number as dates so it gives me all the data. I tried formatting it but that is not working either.
 
Upvote 0
Do you have two table fields or just one? What are the names of the field(s)? What kind of data is in them?

I have to ask since if you have text data you may end up with all kinds of inputs.
By which I mean, Quarters might be entered as "Quarter1", "Q1", "Quarter 1", "Q 1", "1", "One". And years might be entered as "2018" or "18"

This will be better with *your* test data but since I don't have it I'll create my own test data!

So,
assume a table called tbl4 with quarters like this: {"Q1", "Q2", "Q3", "Q4"}, and years like this ("2018", "2019", ...).
We will call the fields CompletedQuarter and CompletedYear.
EDIT: Note that quarters stored as simply {"1", "2", "3", "4"} should also work here too, as well as {"01", "02", "03", "04"}.
then an example of creating a date out of the text values for quarters and years would be:
Code:
SELECT 
	T.*
FROM 
(
	select
		DateSerial([CompletedYear], CInt(Right("0" + [CompletedQuarter], 1)*3)+1, 0) as MyDate
	from
		tbl4
	where 
		(
			Nz(CompletedYear,"") <> "" 
			and 
			Nz(CompletedQuarter,"") <> ""
		)
	UNION ALL
	select
		Null as MyDate
	from
		tbl4
	where 
		(
			Nz(CompletedYear,"") = "" 
			or 
			Nz(CompletedQuarter,"") = ""
) as T
ORDER BY
	T.MyDate

By way of example for someone else looking at this who might have integers instead of text,
so assume a second table called tbl5 with quarters like this: {1, 2, 3, 4}, and years like this (2018, 2019, ...).
Here too the fields are called CompletedQuarter and CompletedYear.
then an example of creating a date out of the integer values for quarters and years would be:
Code:
SELECT
	T.* 
FROM 
(
	select
		DateSerial([CompletedYear], (([CompletedQuarter]*3)+1), 0)as MyDate
	from
		tbl5
	where 
		Nz(CompletedYear, 0) <> 0 
		and Nz(CompletedQuarter, 0) <> 0
	UNION ALL
	select
		Null as MyDate
	from
		tbl4
	where
		(
		(nz(CompletedYear, 0) = 0)
		 or 
		(nz(CompletedQuarter, 0) = 0)
		)
) as T
ORDER BY
	T.MyDate

In both cases the last day of the month is determined in the slightly non-intuitive way of getting the first day of the *next* month and then subtracting one (which is why in the DateSerial() formulas we add 1 to the month (the next month) and take the 0th day (the day before the first day). This is just an old hack in VB for working with dates. This way we don't have to worry whether months have 30 days or 31 days, or even 28 or 29 days.

Also in both cases we don't want null to show up as a weird date like "12/31/1999" so the strategy is to partition the data into two sets: those that have years and quarters, and those that don't. These should be two sets that together make up the entire data set so when union-ed together you have complete data again (if you take all rows with no nulls in either year or quarter, and add to that all rows with a null in one or both of those two fields, then you should have all rows in the table).

Finally, since it's a little weird working with UNION and you might be interested in doing things like ordering the results, the entire thing is wrapped up a a query on a subquery so we can add an order by at the end. The essential pattern of this overall query of a (sub)query is very simple:
Code:
select T.* from (subquery) as T order by X,Y,Z

EDIT: I can't stress enough how important it is to provide information about your data, so we can write the proper solutions out and test them on your data! I've given examples with my own made up test data, but it's practically guaranteed that this will all have to be tweaked or even started over from scratch, because your data will not be the same as the data that I've created. So always give examples and even better names and datatype information for all you relevant tables and fields.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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