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 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")

 

[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")

 

[gymwrecker]

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

 

[gymwrecker]

DRSteele, I got it to work! Thank you! I'm using the following formula:

="Approved by "&Z2&" on "&TEXT(Z15,"mm/dd/yyyy, H:mm am/pm")

Where Z2 has my name, and Z15 =now()

 

[Dave Patton]

N.B. Your Regional Settings and requirements may or may not require alternative formatting.
Your first post stated "05 May 23, 12:12 PM"

Try the following on your system

Time 2023.xlsm
	Z	AA
15	05-05-23 15:16	05 May 23, 3:16 PM
16	02-05-23 09:07	02 May 23, 9:07 AM
17
1g

Cell Formulas
Range	Formula
Z15	Z15	=NOW()
AA15:AA16	AA15	=TEXT(Z15,"dd mmmm yy, h:mm Am/PM")

 

[gymwrecker]

I'm still using ="Approved by "&Z2&" on "&TEXT(Z15,"mm/dd/yyyy, H:mm am/pm") where Z2 has my name and Z15 "=NOW()"

"Approved by John Doe on 05/08/23, 8:17 PM"

This works for me; I just need for the "time stamp" to reflect the time group when last updated and not the "actual" time that changes when page is refreshed?

 

[awoohaw]

I'm still using ="Approved by "&Z2&" on "&TEXT(Z15,"mm/dd/yyyy, H:mm am/pm") where Z2 has my name and Z15 "=NOW()"

"Approved by John Doe on 05/08/23, 8:17 PM"

This works for me; I just need for the "time stamp" to reflect the time group when last updated and not the "actual" time that changes when page is refreshed?

not sure this is what you need completely. But, post #4 requires the entry of the time the person types in their name. There is data validation built around the entry that I think does a pretty good job of being sure the data is collected all at once.

It does NOT use the TODAY() function, but requires the user to type in (by keyboard shortcut or manual) the date of the current day which is compared to in the data validation.

 

[gymwrecker]

awoohaw, how can I add the time stamp to it? The time of the update and not the actual time?