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]

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.

 

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