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?

 

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

 

[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

 

[awoohaw]

Ok, i hope this works for you, it just did for me:

mr excel questions 34.xlsm
	A	B
1
2
3	Date:	2023-05-09 13:27
4	Status:	Approved
5	Approved BY:	gymwrecker
6
7		Approved by gymwrecker on 09 May, 2023 @ 13:27: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=TODAY(),B4="Approved")

 

[awoohaw]

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

please copy and paste your exact formula in cell B7?

 

[awoohaw]

and how are you entering the date and time in cell B3?

 

[awoohaw]

change the format of your cell B3 to: custom format: yyyy-mm-dd hh:mm:ss or mm-dd-yyyy hh:mm:ss or dd-mm-yyyy hh:mm:ss
Whichever format you want. but show the time portion.