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?

 

[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?

 

[gymwrecker]

I did awoohaw, and it works. Thank you!

Just curious if I can also add the time stamp to it. Something like this:

Approved by Mr. John Doe on 09 May, 2023 @12:02

 

[awoohaw]

yes, when you enter the date in B3 also add the time component.
Keyboard shortcut would be CTRL-SEMI-COLON space CTRL-COLON (CTRL-SHFT-SEMI-COLON).

you need to change the validation rule for cell B3 substantially. This validates to a 5 minute before and after (you could probably change it to 1 minute if you want. See below:

mr excel questions 34.xlsm
	A	B
1
2
3	Date:	2023-05-09 12:26
4	Status:	Approved
5	Approved BY:	gymwrecker
6
7		Approved by gymwrecker on 09 May, 2023 @ 12:26: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	Date	not between (NOW()-INT(NOW())-(5/(60*24))) and (NOW()-INT(NOW())+(5/(60*24)))
B4	Custom	=AND(LEN(B4)>0,B4="Approved")
B5	Custom	=AND(B3=TODAY(),B4="Approved")

 

[gymwrecker]

I used the suggested data validation and I'm still having a problem with the time, not sure what is it that I'm doing wrong or missing:

DATE:	2023/05/09
STATUS:	Approved
APPROVED BY:	Mr. John Doe
	Approved by Mr. John Doe on 09 May, 2023 @ 00:00:00

 

[awoohaw]

sorry, although we are using dates and times, the validation doesn't seem to like it. So I just am using a custom value.

... working on it!.... sorry

 

[gymwrecker]

Thank you awoohaw! I fixed it, but still showing the time as 00:00:00

Approved by Mr. John Doe on 09 May 2023 @ 00:00:00