auto filling cells based on preset format

[jbrown021286]

TECH HOURS WORKSHEET 5.0 test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	01JAN24	02JAN24	03JAN24	04JAN24	05JAN24	06JAN24	07JAN24	08JAN24	09JAN24	10JAN24	11JAN24	12JAN24	13JAN24	14JAN24	15JAN24	16JAN24	17JAN24	18JAN24	19JAN24	20JAN24	21JAN24	22JAN24	23JAN24	24JAN24	25JAN24	26JAN24	27JAN24	28JAN24	29JAN24	30JAN25	31JAN26
2	1-Jan	2-Jan	3-Jan	4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	9-Jan	10-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	16-Jan	17-Jan	18-Jan	19-Jan	20-Jan	21-Jan	22-Jan	23-Jan	24-Jan	25-Jan	26-Jan	27-Jan	28-Jan	29-Jan	30-Jan	31-Jan
3
4	01FEB24	02FEB24	03FEB24	04FEB24	05FEB24	06FEB24	07FEB24	08FEB24	09FEB24	10FEB24	11FEB24	12FEB24	13FEB24	14FEB24	15FEB24	16FEB24	17FEB24	18FEB24	19FEB24	20FEB24	21FEB24	22FEB24	23FEB24	24FEB24	25FEB24	26FEB24	27FEB24	28FEB24	29FEB24
5	1-Feb	2-Feb	3-Feb	4-Feb	5-Feb	6-Feb	7-Feb	8-Feb	9-Feb	10-Feb	11-Feb	12-Feb	13-Feb	14-Feb	15-Feb	16-Feb	17-Feb	18-Feb	19-Feb	20-Feb	21-Feb	22-Feb	23-Feb	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb	29-Feb
6
7	01MAR24	02MAR24	03MAR24	04MAR24	05MAR24	06MAR24	07MAR24	08MAR24	09MAR24	10MAR24	11MAR24	12MAR24	13MAR24	14MAR24	15MAR24	16MAR24	17MAR24	18MAR24	19MAR24	20MAR24	21MAR24	22MAR24	23MAR24	24MAR24	25MAR24	26MAR24	27MAR24	28MAR24	29MAR24	30MAR24	31MAR24
8	1-Mar	2-Mar	3-Mar	4-Mar	5-Mar	6-Mar	7-Mar	8-Mar	9-Mar	10-Mar	11-Mar	12-Mar	13-Mar	14-Mar	15-Mar	16-Mar	17-Mar	18-Mar	19-Mar	20-Mar	21-Mar	22-Mar	23-Mar	24-Mar	25-Mar	26-Mar	27-Mar	28-Mar	29-Mar	30-Mar	31-Mar
9
10	01APR24	02APR24	03APR24	04APR24	05APR24	06APR24	07APR24	08APR24	09APR24	10APR24	11APR24	12APR24	13APR24	14APR24	15APR24	16APR24	17APR24	18APR24	19APR24	20APR24	21APR24	22APR24	23APR24	24APR24	25APR24	26APR24	27APR24	28APR24	29APR24	30APR24
11	1-Apr	2-Apr	3-Apr	4-Apr	5-Apr	6-Apr	7-Apr	8-Apr	9-Apr	10-Apr	11-Apr	12-Apr	13-Apr	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	23-Apr	24-Apr	25-Apr	26-Apr	27-Apr	28-Apr	29-Apr	30-Apr
12
13	01MAY24	02MAY24	03MAY24	04AMAY4	05MAY24	06MAY24	07MAY24	08MAY24	09MAY24	10MAY24	11MAY24	12MAY24	13MAY24	14MAY24	15MAY24	16MAY24	17MAY24	18MAY24	19MAY24	20MAY24	21MAY24	22MAY24	23MAY24	24MAY24	25MAY24	26MAY24	27MAY24	28MAY24	29MAY24	30MAY24	31MAY24
14	1-May	2-May	3-May	4-May	5-May	6-May	7-May	8-May	9-May	10-May	11-May	12-May	13-May	14-May	15-May	16-May	17-May	18-May	19-May	20-May	21-May	22-May	23-May	24-May	25-May	26-May	27-May	28-May	29-May	30-May	31-May
15
16	01JUN24	02JUN24	03JUN24	04JUN24	05JUN24	06JUN24	07JUN24	08JUN24	09JUN24	10JUN24	11JUN24	12JUN24	13JUN24	14JUN24	15JUN24	16JUN24	17JUN24	18JUN24	19JUN24	20JUN24	21JUN24	22JUN24	23JUN24	24JUN24	25JUN24	26JUN24	27JUN24	28JUN24	29JUN24	30JUN24
17	1-Jun	2-Jun	3-Jun	4-Jun	5-Jun	6-Jun	7-Jun	8-Jun	9-Jun	10-Jun	11-Jun	12-Jun	13-Jun	14-Jun	15-Jun	16-Jun	17-Jun	18-Jun	19-Jun	20-Jun	21-Jun	22-Jun	23-Jun	24-Jun	25-Jun	26-Jun	27-Jun	28-Jun	29-Jun	30-Jun
18
19	01JUL24	02JUL24	03JUL24	04JUL24	05JUL24	06JUL24	07JUL24	08JUL24	09JUL24	10JUL24	11JUL24	12JUL24	13JUL24	14JUL24	15JUL24	16JUL24	17JUL24	18JUL24	19JUL24	20JUL24	21JUL24	22JUL24	23JUL24	24JUL24	25JUL24	26JUL24	27JUL24	28JUL24	29JUL24	30JUL24	31JUL24
20	1-Jul	2-Jul	3-Jul	4-Jul	5-Jul	6-Jul	7-Jul	8-Jul	9-Jul	10-Jul	11-Jul	12-Jul	13-Jul	14-Jul	15-Jul	16-Jul	17-Jul	18-Jul	19-Jul	20-Jul	21-Jul	22-Jul	23-Jul	24-Jul	25-Jul	26-Jul	27-Jul	28-Jul	29-Jul	30-Jul	31-Jul
21
22	01AUG24	02AUG24	03AUG24	04AUG24	05AUG24	06AUG24	07AUG24	08AUG24	09AUG24	10AUG24	11AUG24	12AUG24	13AUG24	14AUG24	15AUG24	16AUG24	17AUG24	18AUG24	19AUG24	20AUG24	21AUG24	22AUG24	23AUG24	24AUG24	25AUG24	26AUG24	27AUG24	28AUG24	29AUG24	30AUG24	31AUG24
23	1-Aug	2-Aug	3-Aug	4-Aug	5-Aug	6-Aug	7-Aug	8-Aug	9-Aug	10-Aug	11-Aug	12-Aug	13-Aug	14-Aug	15-Aug	16-Aug	17-Aug	18-Aug	19-Aug	20-Aug	21-Aug	22-Aug	23-Aug	24-Aug	25-Aug	26-Aug	27-Aug	28-Aug	29-Aug	30-Aug	31-Aug
24
25	01SEP24	02SEP24	03SEP24	04SEP24	05SEP24	06SEP24	07SEP24	08SEP24	09SEP24	10SEP24	11SEP24	12SEP24	13SEP24	14SEP24	15SEP24	16SEP24	17SEP24	18SEP24	19SEP24	20SEP24	21SEP24	22SEP24	23SEP24	24SEP24	25SEP24	26SEP24	27SEP24	28SEP24	29SEP24	30SEP24
26	1-Sep	2-Sep	3-Sep	4-Sep	5-Sep	6-Sep	7-Sep	8-Sep	9-Sep	10-Sep	11-Sep	12-Sep	13-Sep	14-Sep	15-Sep	16-Sep	17-Sep	18-Sep	19-Sep	20-Sep	21-Sep	22-Sep	23-Sep	24-Sep	25-Sep	26-Sep	27-Sep	28-Sep	29-Sep	30-Sep
27
28	01OCT24	02OCT24	03OCT24	04OCT24	05OCT24	06OCT24	07OCT24	08OCT24	09OCT24	10OCT24	11OCT24	12OCT24	13OCT24	14OCT24	15OCT24	16OCT24	17OCT24	18OCT24	19OCT24	20OCT24	21OCT24	22OCT24	23OCT24	24OCT24	25OCT24	26OCT24	27OCT24	28OCT24	29OCT24	30OCT24	31OCT24
29	1-Oct	2-Oct	3-Oct	4-Oct	5-Oct	6-Oct	7-Oct	8-Oct	9-Oct	10-Oct	11-Oct	12-Oct	13-Oct	14-Oct	15-Oct	16-Oct	17-Oct	18-Oct	19-Oct	20-Oct	21-Oct	22-Oct	23-Oct	24-Oct	25-Oct	26-Oct	27-Oct	28-Oct	29-Oct	30-Oct	31-Oct
30
31	01NOV24	02NOV24	03NOV24	04NOV24	05NOV24	06NOV24	07NOV24	08NOV24	09NOV24	10NOV24	11NOV24	12NOV24	13NOV24	14NOV24	15NOV24	16NOV24	17NOV24	18NOV24	19NOV24	20NOV24	21NOV24	22NOV24	23NOV24	24NOV24	25NOV24	26NOV24	27NOV24	28NOV24	29NOV24	30NOV24
32	1-Nov	2-Nov	3-Nov	4-Nov	5-Nov	6-Nov	7-Nov	8-Nov	9-Nov	10-Nov	11-Nov	12-Nov	13-Nov	14-Nov	15-Nov	16-Nov	17-Nov	18-Nov	19-Nov	20-Nov	21-Nov	22-Nov	23-Nov	24-Nov	25-Nov	26-Nov	27-Nov	28-Nov	29-Nov	30-Nov
33
34	01DEC24	02DEC24	03DEC24	04DEC24	05DEC24	06DEC24	07DEC24	08DEC24	09DEC24	10DEC24	11DEC24	12DEC24	13DEC24	14DEC24	15DEC24	16DEC24	17DEC24	18DEC24	19DEC24	20DEC24	21DEC24	22DEC24	23DEC24	24DEC24	25DEC24	26DEC24	27DEC24	28DEC24	29DEC24	30DEC24	31DEC24
35	1-Dec	2-Dec	3-Dec	4-Dec	5-Dec	6-Dec	7-Dec	8-Dec	9-Dec	10-Dec	11-Dec	12-Dec	13-Dec	14-Dec	15-Dec	16-Dec	17-Dec	18-Dec	19-Dec	20-Dec	21-Dec	22-Dec	23-Dec	24-Dec	25-Dec	26-Dec	27-Dec	28-Dec	29-Dec	30-Dec	31-Dec
Date List

this is a list of dates with the above row the short hand used in a report. i am trying to find an efficient way to change them all over at the start of the year so I don't have to change them all cell by cell. the format of the short hand is digits 1 and 2 are the day 3, 4 and 5 are for the month and 6 and 7 correspond with the year. the flash fill function doesn't work for this since the first 2 digits would need to be the ones that counted up not the last 2. any suggestions would be appreciated

 

[HighAndWilder]

Does this work for you?
Paste the formula:

=LET(y,2024,m,INT((ROW()-1)/3)+1,l,MOD(ROW(),3),d,DATE(y,m,COLUMN()),em,DAY(EOMONTH(d,0)),IF(l>0,IF(MONTH(d)=m,d,""),""))

into cell A1 and copy across to column AE and down to row 35.
Is having the month name in proper case a problem. e.g. 19Jun2024
Do you want the cells with dates to behave as dates or as text?

The first Conditional Format should be ddmmmyy and the second dd-mmm.

Book1
	A	B	C	D	E	F	G	H
1	01Jan24	02Jan24	03Jan24	04Jan24	05Jan24	06Jan24	07Jan24	08Jan24
2	01-Jan	02-Jan	03-Jan	04-Jan	05-Jan	06-Jan	07-Jan	08-Jan
3
4	01Feb24	02Feb24	03Feb24	04Feb24	05Feb24	06Feb24	07Feb24	08Feb24
5	01-Feb	02-Feb	03-Feb	04-Feb	05-Feb	06-Feb	07-Feb	08-Feb
6
7	01Mar24	02Mar24	03Mar24	04Mar24	05Mar24	06Mar24	07Mar24	08Mar24
8	01-Mar	02-Mar	03-Mar	04-Mar	05-Mar	06-Mar	07-Mar	08-Mar
9
10	01Apr24	02Apr24	03Apr24	04Apr24	05Apr24	06Apr24	07Apr24	08Apr24
11	01-Apr	02-Apr	03-Apr	04-Apr	05-Apr	06-Apr	07-Apr	08-Apr
Sheet2

Cell Formulas
Range	Formula
A1:H11	A1	=LET(y,2024,m,INT((ROW()-1)/3)+1,l,MOD(ROW(),3),d,DATE(y,m,COLUMN()),em,DAY(EOMONTH(d,0)),IF(l>0,IF(MONTH(d)=m,d,""),""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:AE36	Expression	=MOD(ROW(),3)=1	text	NO
A1:AE36	Expression	=MOD(ROW(),3)=2	text	NO