Quarter from date in another column

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
58
Hi everyone,

I have this code below that for the most part works well but needs help. When I have dates for employees in any column (it does float so not always in col C) i need to know the Time Period in the cell to the left. This list can be quite long or as short as you see now. Currently with the code below, I highlight the entire column and run the vba which inserts a column to the left and the the verbage needed depending on the date. Most of the time it works. Problem I run into is sometimes it doesn't work at all and i don't know why. But it also pastes the results as formulas instead of values. I am not sure what to change to get it to always work and also to put the results in as values instead of formulas so any and all help is greatly appreciated.

Before:

Worker IDWorker NameCheck Date
1027Sorensen,Robert06/15/2021
1027Sorensen,Robert07/04/2021
1030Dickinson,Timothy06/15/2021
1030Dickinson,Timothy07/04/2021
1034Mooney,Brad 06/15/2021
1034Mooney,Brad 07/04/2021
1041Adams,Kathleen06/15/2021
1041Adams,Kathleen07/04/2021
1041Adams,Kathleen10/01/2021
1042Anderson,Christopher06/15/2021
1042Anderson,Christopher07/04/2021
1055Mills-Halso,Susan06/15/2021
1055Mills-Halso,Susan07/04/2021
1079McBride,Rodney06/15/2021
1079McBride,Rodney07/04/2021
1089Vandewege,Daniele06/15/2021
1089Vandewege,Daniele07/04/2021
1101Schricker Jr,Jerry06/15/2021
1101Schricker Jr,Jerry07/04/2021
1133Misiewicz,Stacey06/15/2021
1133Misiewicz,Stacey07/04/2021
1184Tanis,Wendy 06/15/2021
1184Tanis,Wendy 07/04/2021
1185Priebe,Dean06/15/2021


After:
12114820 YTD Import.xlsx
ABCD
1Worker IDWorker NameTime PeriodCheck Date
21027Sorensen,RobertCheck Date - Qtr 206/15/2021
31027Sorensen,RobertCheck Date - Qtr 307/04/2021
41030Dickinson,TimothyCheck Date - Qtr 206/15/2021
51030Dickinson,TimothyCheck Date - Qtr 307/04/2021
61034Mooney,Brad Check Date - Qtr 206/15/2021
71034Mooney,Brad Check Date - Qtr 307/04/2021
81041Adams,KathleenCheck Date - Qtr 206/15/2021
91041Adams,KathleenCheck Date - Qtr 307/04/2021
101041Adams,KathleenCheck Date - Qtr 410/01/2021
111042Anderson,ChristopherCheck Date - Qtr 206/15/2021
121042Anderson,ChristopherCheck Date - Qtr 307/04/2021
131055Mills-Halso,SusanCheck Date - Qtr 206/15/2021
141055Mills-Halso,SusanCheck Date - Qtr 307/04/2021
151079McBride,RodneyCheck Date - Qtr 206/15/2021
161079McBride,RodneyCheck Date - Qtr 307/04/2021
171089Vandewege,DanieleCheck Date - Qtr 206/15/2021
181089Vandewege,DanieleCheck Date - Qtr 307/04/2021
191101Schricker Jr,JerryCheck Date - Qtr 206/15/2021
201101Schricker Jr,JerryCheck Date - Qtr 307/04/2021
211133Misiewicz,StaceyCheck Date - Qtr 206/15/2021
221133Misiewicz,StaceyCheck Date - Qtr 307/04/2021
231184Tanis,Wendy Check Date - Qtr 206/15/2021
241184Tanis,Wendy Check Date - Qtr 307/04/2021
251185Priebe,DeanCheck Date - Qtr 206/15/2021
Sheet3
Cell Formulas
RangeFormula
C2:C25C2="Check Date - Qtr "&ROUNDUP(MONTH(D2)/3,0)


VBA Code:
Sub PPP_Time_Period()



Dim sht As Worksheet

Dim lastrow As Long



Set sht = ActiveSheet



lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row



Selection.Insert Shift:=xlToRight

ActiveCell = "Time Period"

ActiveCell.Offset(1, 0).FormulaR1C1 = "=""Check Date - Qtr ""&ROUNDUP(MONTH(RC[1])/3,0)"

ActiveCell.Offset(1, 0).Copy Range(ActiveCell.Offset(1, 0), Cells(lastrow, ActiveCell.Column))

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,571
Office Version
  1. 2007
Platform
  1. Windows
When I have dates for employees in any column (it does float so not always in col C)

It means you have something like this:
Dante Amor.xlsm
ABCDEFG
1Worker IDWorker NameCheck Date
21027Sorensen,Robert15/06/2021
31027Sorensen,Robert04/07/2021
41030Dickinson,Timothy15/06/2021
51030Dickinson,Timothy04/07/2021
61034Mooney,Brad 15/06/2021
71034Mooney,Brad 04/07/2021
81041Adams,Kathleen15/06/2021
91041Adams,Kathleen04/07/2021
101041Adams,Kathleen01/10/2021
111042Anderson,Christopher15/06/2021
121042Anderson,Christopher04/07/2021
131055Mills-Halso,Susan15/06/2021
141055Mills-Halso,Susan04/07/2021
151079McBride,Rodney15/06/2021
161079McBride,Rodney04/07/2021
171089Vandewege,Daniele15/06/2021
181089Vandewege,Daniele04/07/2021
Hoja6


Then try this:

VBA Code:
Sub PPP_Time_Period()
  Dim lc As Long

  Range("C:C").Insert Shift:=xlToRight
  Range("C1").Value = "Time Period"
  lc = ActiveSheet.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

  With Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=""Check Date - Qtr ""&ROUNDUP(MONTH(MAX(RC[1]:RC[" & lc & "]))/3,0)"
    .Value = .Value
  End With
End Sub

Result:
Dante Amor
ABCDEFGH
1Worker IDWorker NameTime PeriodCheck Date
21027Sorensen,RobertCheck Date - Qtr 215/06/2021
31027Sorensen,RobertCheck Date - Qtr 304/07/2021
41030Dickinson,TimothyCheck Date - Qtr 215/06/2021
51030Dickinson,TimothyCheck Date - Qtr 304/07/2021
61034Mooney,Brad Check Date - Qtr 215/06/2021
71034Mooney,Brad Check Date - Qtr 304/07/2021
81041Adams,KathleenCheck Date - Qtr 215/06/2021
91041Adams,KathleenCheck Date - Qtr 304/07/2021
101041Adams,KathleenCheck Date - Qtr 401/10/2021
111042Anderson,ChristopherCheck Date - Qtr 215/06/2021
121042Anderson,ChristopherCheck Date - Qtr 304/07/2021
131055Mills-Halso,SusanCheck Date - Qtr 215/06/2021
141055Mills-Halso,SusanCheck Date - Qtr 304/07/2021
151079McBride,RodneyCheck Date - Qtr 215/06/2021
161079McBride,RodneyCheck Date - Qtr 304/07/2021
171089Vandewege,DanieleCheck Date - Qtr 215/06/2021
181089Vandewege,DanieleCheck Date - Qtr 304/07/2021
Hoja6
 

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
58
Thank you. I seem to have been unclear when saying the date floats. I mean sometimes all the dates are in column C. Sometimes i have data feed in depending on the report i am converting and the dates are now all in column D. They are all in the same column but they are not always in column C. I don't need the "Time Period" column in column C either, i just need it adjacent to the date column. As I said, for the most part the code works fine. Just looking to make sure it's only dependent on looking to the cell immediately to the right for the date and then paste the formula as a value.

Would adding .Value = .Value do that part?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,571
Office Version
  1. 2007
Platform
  1. Windows
I seem to have been unclear when saying the date floats. I mean sometimes all the dates are in column C. Sometimes i have data feed in depending on the report i am converting and the dates are now all in column D.
How to know if it is column C or D, it will always be the last column of the sheet from right to left?
 

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
58

ADVERTISEMENT

How to know if it is column C or D, it will always be the last column of the sheet from right to left?
so currently, i just select the column then run the vba
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,457
Office Version
  1. 365
Platform
  1. Windows
Can be achieved quickly with Power Query. Here is the Mcode from the UI
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Worker ID", Int64.Type}, {"Worker Name", type text}, {"Check Date", type date}}),
    #"Inserted Quarter" = Table.AddColumn(#"Changed Type", "Quarter", each Date.QuarterOfYear([Check Date]), Int64.Type),
    #"Added Prefix" = Table.TransformColumns(#"Inserted Quarter", {{"Quarter", each "Check Date - Qtr " & Text.From(_, "en-US"), type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Prefix",{"Worker ID", "Worker Name", "Quarter", "Check Date"})
in
    #"Reordered Columns"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,571
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Ok, check this:

VBA Code:
Sub PPP_Time_Period()
  Dim lastrow As Long
  Dim col As Long
  
  lastrow = Cells(Rows.Count, "A").End(xlUp).Row
  col = ActiveCell.Column
  Selection.Insert Shift:=xlToRight
  ActiveCell = "Time Period"
  
  With Range(Cells(2, col), Cells(lastrow, col))
    .FormulaR1C1 = "=""Check Date - Qtr ""&ROUNDUP(MONTH(RC[1])/3,0)"
    .Value = .Value
  End With
End Sub
 

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
58
Ok, check this:

VBA Code:
Sub PPP_Time_Period()
  Dim lastrow As Long
  Dim col As Long
 
  lastrow = Cells(Rows.Count, "A").End(xlUp).Row
  col = ActiveCell.Column
  Selection.Insert Shift:=xlToRight
  ActiveCell = "Time Period"
 
  With Range(Cells(2, col), Cells(lastrow, col))
    .FormulaR1C1 = "=""Check Date - Qtr ""&ROUNDUP(MONTH(RC[1])/3,0)"
    .Value = .Value
  End With
End Sub
PERFECT! Thank you for the assist.
 

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
58
Thank you. My only problem with PQ is that with vba i can create a ribbon tab so that coworkers (most can barely spell Excel) can push a button after some other formatting and scrubbing and achieve what is needed.

But thank you.
Can be achieved quickly with Power Query. Here is the Mcode from the UI
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Worker ID", Int64.Type}, {"Worker Name", type text}, {"Check Date", type date}}),
    #"Inserted Quarter" = Table.AddColumn(#"Changed Type", "Quarter", each Date.QuarterOfYear([Check Date]), Int64.Type),
    #"Added Prefix" = Table.TransformColumns(#"Inserted Quarter", {{"Quarter", each "Check Date - Qtr " & Text.From(_, "en-US"), type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Prefix",{"Worker ID", "Worker Name", "Quarter", "Check Date"})
in
    #"Reordered Columns"
 

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
with power query its the same.
just paste new data into the same table and refresh the sheet ctrl+alt+f5 and it'll reload with new info.
 

Forum statistics

Threads
1,141,062
Messages
5,704,060
Members
421,325
Latest member
tapete86

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
Top