Quarter from date in another column

S

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
155
Office Version
  1. 2016
Platform
  1. Windows
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Serafin54 said:
When I have dates for employees in any column (it does float so not always in col C)
Click to expand...

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
 
Upvote 0
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?
 
Upvote 0
Serafin54 said:
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.
Click to expand...
How to know if it is column C or D, it will always be the last column of the sheet from right to left?
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
DanteAmor said:
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
Click to expand...
PERFECT! Thank you for the assist.
 
Upvote 0
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.
alansidman said:
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"
Click to expand...
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
153
XLfriend777
XLfriend777
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
305
Peter_SSs
Peter_SSs
N
  • Solved
loop though dates column and if matched get value from the next column
Replies
2
Views
286
Dave Patton
D
F
  • Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
224
freeb1893
F
O
  • Question
MS Excel VBA Code - Simplest Way to Check if a Series of Dates is Between Two Dates and Enter Comment and Date in another Column
Replies
3
Views
710
bebo021999
B

Forum statistics

Threads
1,214,813
Messages
6,121,706
Members
449,049
Latest member
THMarana

Share this page

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