Convert Quarter/Year Format to a Date

Convert quarter/year format to a date

1) The zoo package has a "yearqtr" class. Convert to that and then to "Date" class:

library(zoo)
x <- c("Q1/13","Q2/14")

as.Date(as.yearqtr(x, format = "Q%q/%y"))
## [1] "2013-01-01" "2014-04-01"

2) Alternately use this to get the last day of the quarter instead of the first:

as.Date(as.yearqtr(x, format = "Q%q/%y"), frac = 1)
## [1] "2013-03-31" "2014-06-30"

3) Also consider not converting to "Date" class at all and just using "yearqtr" class directly:

as.yearqtr(x, format = "Q%q/%y")
## [1] "2013 Q1" "2014 Q2"

convert quarter year to last date of quarter in R

We can convert to Date with zoo and get the last date of the quarter with frac. We use some RegEx to rearrange in zoo's suitable format:

df$TIME=as.Date(as.yearqtr(gsub("(\\d)(Q)(\\d{1,})","\\3 Q\\1",df$TIME)),frac = 1)
 df
        TIME VALUE
1 2019-03-31     1
2 2019-06-30     2
3 2019-09-30     3
4 2019-12-31     4

Data:

df <-structure(list(TIME = structure(1:4, .Label = c("1Q2019", "2Q2019", 
"3Q2019", "4Q2019"), class = "factor"), VALUE = 1:4), class = "data.frame", row.names = c(NA, 
-4L))

Convert Quarter + Year to a date

In Microsoft SQL Server you can do this as followed:

First I create a date as varchar which I then convert to the Date datatype. After this I format it to the desired format. I use a CASE Clause to get the correct month by the quarter.

SELECT FORMAT(CONVERT(DATE,CAST([Year] AS VARCHAR(4))+'-'+
                    CAST(CASE
                         WHEN [Quarter] = 'Q1' THEN '01' 
                         WHEN [Quarter] = 'Q2' THEN '04'
                         WHEN [Quarter] = 'Q3' THEN '07'
                         WHEN [Quarter] = 'Q4' THEN '10' END AS VARCHAR(2))+'-'+
                    CAST('01' AS VARCHAR(2))), 'dd-MM-yyyy', 'en-us')
                    from dates

Convert quarter-year to MM/DD/YYYY in Excel

Try

=DATE(RIGHT(A1,4),(MID(A1,2,1)*3)-2,1)

That will return a date. Format to display in whatever date format you want.

Formula to convert quarter-year to MM/DD/YYYY in Excel

Try this:

=Date(Right(A1,2)+2000,CHOOSE(Left(A1,1),1,4,7,10),1)

Turn character YYYY-Q1 into date

You have to use the right format in as.yearqtr formula like this:

library(zoo)
x<-"2010-Q1"
as.yearqtr(x, format = "%Y-Q%q")
[1] "2010 Q1"

convert quarter-year to month-end date in SQL

You can use this. As the dates are fixed and don't move

SET @date := "1Q1993"

SELECT CONCAT(SUBSTRING_INDEX(@date,'Q',-1),
CASE SUBSTRING_INDEX(@date,'Q',1) 
       WHEN 1 THEN '-03-31'
       WHEN 2 THEN '-06-30'
       WHEN 3 THEN '-09-30'
       ELSE  '-12-31' END)

| CONCAT(SUBSTRING_INDEX(@date,'Q',-1),
CASE SUBSTRING_INDEX(@date,'Q',1) 
      WHEN 1 THEN '-03-31'
      WHEN 2 THEN '-06-30'
      WHEN 3 THEN '-09-30'
      ELSE  '-12-31' END) |
| >:--------------------------------------------------------------------------------------->---------------------------------------------------------------------------------------->------- |
| 1993-03-31                                                                                                                                                                              |

db<>fiddle here

Converting a Year-quarter string to date object in R

Simplest with {lubridate}

library(lubridate)

yq(dat)
#[1] "2008-01-01"

Alternative with {zoo}

library(zoo)

as.Date(as.yearqtr(dat, format = "%YQ%q"))

#[1] "2008-01-01"

Convert character in format YEARQT to a quarterly date in R

You almost got it! The format needs to be sligthly adapted.

%YQ%q: %Y stands for the year, Q stands for the Q in your inital format and %q stands for the quarter.

Code

library(zoo)

DATA.QTR <- DATA.QTR %>% mutate(QUARTER = as.Date(as.yearqtr(format(Date), "%YQ%q")))

Output

> DATA.QTR
# A tibble: 6 x 2
  Date   QUARTER   
  <chr>  <date>    
1 1960Q1 1960-01-01
2 1960Q2 1960-04-01
3 1960Q3 1960-07-01
4 1960Q4 1960-10-01
5 1961Q1 1961-01-01
6 1961Q2 1961-04-01

Data

DATA.QTR <- structure(list(Date = c("1960Q1", "1960Q2", "1960Q3", "1960Q4", 
                                    "1961Q1", "1961Q2")), class = c("tbl_df", "tbl", "data.frame"
                                    ), row.names = c(NA, -6L))

Clean way to convert quarterly periods to datetime in pandas

You can (and should) use pd.PeriodIndex as a first step, then convert to timestamp using PeriodIndex.to_timestamp:

qs = df['Quarter'].str.replace(r'(Q\d) (\d+)', r'\2-\1')
qs

0    1996-Q3
1    1996-Q4
2    1997-Q1
Name: Quarter, dtype: object

df['date'] = pd.PeriodIndex(qs, freq='Q').to_timestamp()
df

   Quarter       date
0  Q3 1996 1996-07-01
1  Q4 1996 1996-10-01
2  Q1 1997 1997-01-01

The initial replace step is necessary as PeriodIndex expects your periods in the %Y-%q format.

---

Another option is to use pd.to_datetime after performing string replacement in the same way as before.

df['date'] = pd.to_datetime(
    df['Quarter'].str.replace(r'(Q\d) (\d+)', r'\2-\1'), errors='coerce')
df

   Quarter       date
0  Q3 1996 1996-07-01
1  Q4 1996 1996-10-01
2  Q1 1997 1997-01-01

---

If performance is important, you can split and join, but you can do it cleanly:

df['date'] = pd.to_datetime([
    '-'.join(x.split()[::-1]) for x in df['Quarter']])

df

   Quarter       date
0  Q3 1996 1996-07-01
1  Q4 1996 1996-10-01
2  Q1 1997 1997-01-01

---

Related Topics