Quarter from date in another column

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
69
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,156
Office Version
  1. 2007
Platform
  1. Windows
Again with pleasure. Thanks for the feedback
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,612
Messages
5,765,419
Members
425,286
Latest member
CazzaBabes

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