How to add dates and letters with letters following each other.

[Du Toit]

Hi there.

I am working on a bit of automation.
Lets start with the result that I want.
i need this bellow to auto generate when I drag a cell down.

DS2024/11/28A

DS2024/11/28B

DS2024/11/28C

DS2024/11/28D

DS2024/11/28E

how I do t now is I have the DS part and the Date auto filling from a fixed cell will put the formula bellow. " it is tailored to my sheet"
=$E$2&TEXT($E$3;"yyyy/mm/dd")&TEXT("A"

E2 just has the letters "DS" in it.

what i am not able to figure out is to make the A at the end turn to a B if i drag the cell down.

i also need it to restart at A if the E3 cell changes to the next day. " E3 =today() "

i hope this is possible.

Book1.xlsx
	A	B	C	D	E
1
2					DS
3					2024-11-28
4	DS2024/11/28A
5
6
Sheet1

Cell Formulas
Range	Formula
E3	E3	=TODAY()
A4	A4	=$E$2&TEXT($E$3,"yyyy/mm/dd")&TEXT("A",)

 

[Fluff]

Hi & welcome to MrExcel.
If you never need to go beyond Z then how about

Fluff.xlsm
	A	B	C	D	E
1
2					DS
3					28/11/2024
4	DS2024/11/28A
5	DS2024/11/28B
6	DS2024/11/28C
7	DS2024/11/28D
8	DS2024/11/28E
9	DS2024/11/28F
10	DS2024/11/28G
11	DS2024/11/28H
12	DS2024/11/28I
Sheet6

Cell Formulas
Range	Formula
E3	E3	=TODAY()
A4:A12	A4	=$E$2&TEXT($E$3,"yyyy/mm/dd")&CHAR(ROWS(A$4:A4)+64)

 

[Du Toit]

Hi there.

I am working on a bit of automation.
Lets start with the result that I want.
i need this bellow to auto generate when I drag a cell down.

DS2024/11/28A

DS2024/11/28B

DS2024/11/28C

DS2024/11/28D

DS2024/11/28E

how I do t now is I have the DS part and the Date auto filling from a fixed cell will put the formula bellow. " it is tailored to my sheet"
=$E$2&TEXT($E$3;"yyyy/mm/dd")&TEXT("A"

E2 just has the letters "DS" in it.

what i am not able to figure out is to make the A at the end turn to a B if i drag the cell down.

i also need it to restart at A if the E3 cell changes to the next day. " E3 =today() "

i hope this is possible.

Book1.xlsx
	A	B	C	D	E
1
2					DS
3					2024-11-28
4	DS2024/11/28A
5
6
Sheet1

Cell Formulas
Range	Formula
E3	E3	=TODAY()
A4	A4	=$E$2&TEXT($E$3,"yyyy/mm/dd")&TEXT("A",)

Hi & welcome to MrExcel.
If you never need to go beyond Z then how about

Fluff.xlsm
	A	B	C	D	E
1
2					DS
3					28/11/2024
4	DS2024/11/28A
5	DS2024/11/28B
6	DS2024/11/28C
7	DS2024/11/28D
8	DS2024/11/28E
9	DS2024/11/28F
10	DS2024/11/28G
11	DS2024/11/28H
12	DS2024/11/28I
Sheet6

Cell Formulas
Range	Formula
E3	E3	=TODAY()
A4:A12	A4	=$E$2&TEXT($E$3,"yyyy/mm/dd")&CHAR(ROWS(A$4:A4)+64)

Okay, that could work but how do you get it to restart at A if it is a new day automatically?

 

[Fluff]

It will do that automatically, today the same sheet shows

Fluff.xlsm
	A	B	C	D	E
1
2					DS
3					29/11/2024
4	DS2024/11/29A
5	DS2024/11/29B
6	DS2024/11/29C
7	DS2024/11/29D
8	DS2024/11/29E
9	DS2024/11/29F
10	DS2024/11/29G
11	DS2024/11/29H
12	DS2024/11/29I
Sheet6

Cell Formulas
Range	Formula
E3	E3	=TODAY()
A4:A12	A4	=$E$2&TEXT($E$3,"yyyy/mm/dd")&CHAR(ROWS(A$4:A4)+64)

 

[Du Toit]

It will do that automatically, today the same sheet shows

Fluff.xlsm
	A	B	C	D	E
1
2					DS
3					29/11/2024
4	DS2024/11/29A
5	DS2024/11/29B
6	DS2024/11/29C
7	DS2024/11/29D
8	DS2024/11/29E
9	DS2024/11/29F
10	DS2024/11/29G
11	DS2024/11/29H
12	DS2024/11/29I
Sheet6

Cell Formulas
Range	Formula
E3	E3	=TODAY()
A4:A12	A4	=$E$2&TEXT($E$3,"yyyy/mm/dd")&CHAR(ROWS(A$4:A4)+64)

There is a few problems with this.

the first problem is let's say today I created them to mark certain info.
the next day those dates of yesterday also changed.

so it can't build up a history because all the dates on the sheet will change even those I need to stay on the previous date.

The reason for this is that it is like an order number system.
I gave an order today and it needs to stay the same forever even if the formula is on the next day.

 

[Fluff]

You cannot do that if the formula is built looking at a date in E3. You would need VBA

 

[Du Toit]

You cannot do that if the formula is built looking at a date in E3. You would need VBA

What do you mean by VBA?

 

[saboh12617]

Hello,

You need to use VBA/macros to save the old dates. Otherwise the function TODAY() will recalculate on workbook open/calculation and update your yesterday's dates to today.

With a macro in VBA you can overpass this, for example by copying the cells with formulas and special pasting the values automatically.