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]

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

 

[gymwrecker]

Yes, I copied your formula in B7 w/o; did not change anything. Formatted B3 yyyy/mm/dd h:mm and still shows as 0:00

Note: remove the seconds from the time stamp...

 

[awoohaw]

humor me please ... and post the formula I asked you to do?

Also, please use the formats and formulas as I have them EXACTLY until you get them to work... then you can change them in whatever way you want.

It works for me... It should work for you. So get it to work my way, then you can alter as you wish.

 

[awoohaw]

and what is the value in cell B3? The "quick view" in the formatting pane makes me think you did not add the time.

 

[gymwrecker]

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:

View attachment 91284


Data validation error message for the "Approved" entry:

View attachment 91285



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

awoohaw, it works now!!!! Thank you for your time and patience!! It's greatly appreciated.

 

[awoohaw]

Awesome. Glad it works.
I did see a typo in the error message for cell B5. It says date/time in B5, but should say date/time in B3.

Best Wishes!

 

[gymwrecker]

and what is the value in cell B3? The "quick view" in the formatting pane makes me think you did not add the time.

I had =today() instead of =now() in B3, that's why it was not working.... It's good now!!! TY!!!

 

[awoohaw]

I had =today() instead of =now() in B3, that's why it was not working.... It's good now!!! TY!!!

don't use NOW in that field. You will lose all history.
The whole purpose of using B3 to enter a date was to be sure the value doesn't change with every single calculation or opening of your workbook.

 

[gymwrecker]

You are correct, B7 is changing with every single calculation or when I refresh.... I think the problem I'm having is with B3; no value, I left it blank... although I formatted it correctly; then what should I input in B3 so it wont change or lose my history?

 

[gymwrecker]

Thank you awoohaw! I see now, I must enter "Control+Colon" space "Control+Semi-Colon"...

my intent was to update my workbook with only a copy and paste of the formula (=IF(SUM(--(B3:B5<>""))=3,B4&" by "&B5&" on "&TEXT(B3,"dd mmm, yyyy") & " @ " & TEXT(B3,"hh:mm:ss"),"") without the need of additional keystrokes like control+colon space control-semicolon.... while keeping the history of each update.