Date and Time Stamp

[gymwrecker]

Is it possible to add a formula or instruction to a standard text in excel so when I copy and paste it will automatically add the date and time stamp?

This is a sample of a standard text I use since I approve 100's of personnel actions a day:

"Approved by Mr. John Doe on 05 May 23, 12:12 PM"

Can the underline date and time appear automatically when I copy in any particular cell?

 

[DRSteele]

I mocked this up for you. I hope it helps.

MrExcel posts20.xlsx
	B	C	D
3	Workman	Approved	Output
4	Mr. John Doe	5/5/2023 11:01 AM	Approved by Mr. John Doe on 05/05/2023, 11:01 AM
5	Mrs. Jane Doh	5/5/2023 11:03 AM	Approved by Mrs. Jane Doh on 05/05/2023, 11:03 AM
6	Dr. Jones Dow	5/5/2023 11:07 AM	Approved by Dr. Jones Dow on 05/05/2023, 11:07 AM
Sheet17

Cell Formulas
Range	Formula
D4:D6	D4	="Approved by "&B4&" on "&TEXT(C4,"mm/dd/yyyy, H:mm am/pm")

 

[awoohaw]

here is a better version:

mr excel questions 34.xlsm
	A	B	C	D
1
2
3	Date:	2023-05-05
4	Status:	Approved
5	Approved BY:	gymwrecker
6
7		Approved by gymwrecker on 05 May, 2023
gymwrecker

Cell Formulas
Range	Formula
B7	B7	=IF(SUM(--(B3:B5<>""))=3,B4&" by "&B5&" on "&TEXT(B3,"dd mmm, yyyy"),"")

Cells with Data Validation
Cell	Allow	Criteria
B3	Date	=TODAY()
B4	Custom	=AND(LEN(B4)>0,B4="Approved")
B5	Custom	=AND(B3=TODAY(),B4="Approved")

 

[awoohaw]

have you tried to set up the data validation as I have put in the post?

Here it is again, without the TODAY() function as the value (It still uses the today function in the validation (so it is getting that as a timestamp and it will not change with time):

mr excel questions 34.xlsm
	A	B	C	D	E
1
2
3	Date:	2023-05-05
4	Status:	Approved
5	Approved BY:	gymwrecker
6
7		Approved by gymwrecker on 05 May, 2023
gymwrecker

Cell Formulas
Range	Formula
B7	B7	=IF(SUM(--(B3:B5<>""))=3,B4&" by "&B5&" on "&TEXT(B3,"dd mmm, yyyy"),"")

Cells with Data Validation
Cell	Allow	Criteria
B3	Date	=TODAY()
B4	Custom	=AND(LEN(B4)>0,B4="Approved")
B5	Custom	=AND(B3=TODAY(),B4="Approved")

 

[awoohaw]

I hope all this explains it correctly now. It seems to work for me:
But you should format B3 as a date and time format (to your comfortable format, but should have h:m:s as well).

mr excel questions 34.xlsm
	A	B
1
2
3	Date:	2023-05-09 13:37
4	Status:	Approved
5	Approved BY:	gymwrecker
6
7		Approved by gymwrecker on 09 May, 2023 @ 13:37:00
gymwrecker

Cell Formulas
Range	Formula
B7	B7	=IF(SUM(--(B3:B5<>""))=3,B4&" by "&B5&" on "&TEXT(B3,"dd mmm, yyyy") & " @ " & TEXT(B3,"hh:mm:ss"),"")

Cells with Data Validation
Cell	Allow	Criteria
B3	Custom	=AND(B3>(NOW()-TIME(0,5,0)),B3<(NOW()+TIME(0,5,0)))
B4	Custom	=AND(LEN(B4)>0,B4="Approved")
B5	Custom	=AND(B3>(NOW()-TIME(0,5,0)),B3<(NOW()+TIME(0,5,0)),B4="Approved")

Data validation error message for the date entry:

Data validation error message for the "Approved" entry:

Data validation error message for the Approver's name (this also checks the date again):

 

[awoohaw]

Okay. The approaches although it seems that Date vs DateTime values would be an easy switch this exercise showed me that it is not. So if you do want to convert, you should come back to here for the last set up I posted.

Best wishes!

 

[Dave Patton]

Lambda DGP.xlsm
	A	B	C	D	E	F	G
12		Approved By: gymwrecker 17-Mar-23 3:15 PM
2e

Cell Formulas
Range	Formula
B12	B12	=Stamp(17,3,15,15)

Minor edit to the new function "Stamp"
=LAMBDA(Day,Month,Hour,Minute,LET(d, Day, mo, Month, h, Hour, m, Minute, "Approved By: gymwrecker " & TEXT(DATE(2023, mo, d) + TIME(h, m, 0), "d-mmm-yy h:mm Am/PM")))

N.B. Next year, you will have to edit the year part 2023 to 2024. It could be a new function say Stamp24

 

[Dave Patton]

"Following your example on Post #36, B12 =STAMP(17,3,1515)"

Please check your data entry, the quote above is missing a comma. It should be =STAMP(17,3,15,15)

For a different date and time, you enter or edit the formula with the necessary parameters.

Lambda DGP.xlsm
	A	B	C	D
12		Approved By: gymwrecker 17-Mar-23 3:15 PM
13		Approved By: gymwrecker 18-Mar-23 3:20 PM
14		Approved By: gymwrecker 10-May-23 3:20 PM
15
2e

Cell Formulas
Range	Formula
B12	B12	=Stamp(17,3,15,15)
B13	B13	=Stamp(18,3,15,20)
B14	B14	=Stamp(10,5,15,20)

 

[awoohaw]

Here is a stab at it:
This has data validation to ensure data entered.

mr excel questions 34.xlsm
	A	B	C	D	E
1
2
3	Date:	2023-05-05
4	Status:	Approved
5	Approved BY:	gymwrecker
6
7		Approved by gymwrecker on 05 May, 2023
gymwrecker

Cell Formulas
Range	Formula
B7	B7	=B4 & " by " & B5 & " on " & TEXT(B3,"dd mmm, yyyy")

Cells with Data Validation
Cell	Allow	Criteria
B3	Date	=TODAY()
B4	Custom	=AND(LEN(B4)>0,B4="Approved")
B5	Custom	=AND(B3=TODAY(),B4="Approved")

 

[gymwrecker]

DRSteele, it worked, the only problem is that it returns 12:00 AM every single time.... thoughts?