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]

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.

i thought you made that requirement clear in post #8. I'm curious then, what were you asking about in Post #8?

You could do no date entry in a cell with VBA. But I am not proficient at that. It would be some kind of worksheet change event where it looks for a change in either the approval or name cells and then creates the "approval by and date stamp" as a text and puts it into the desired cell.

 

[gymwrecker]

I'm using the formulas you posted in #11, without the time stamp. The time stamp was nice to have, but I can do with only the date stamp for now. I just need to make sure I don't lose the date stamp history the next day when I update my worksheet with new entries...

I greatly appreciate all the help awoohaw! Thank you!

 

[awoohaw]

Okay. The approaches although it seems that Date vs DateTime values would be an easy switch this exercise showed me that it is not. So if you do want to convert, you should come back to here for the last set up I posted.

Best wishes!

 

[Dave Patton]

I have not reviewed all the posts in this thread.
Since Now is volatile, we cannot use it.

I drafted a Lambda function the may help entering the Date+Time.

I named the function DateTime but you can use your preferred name.
DateTime shows the required parameters, I could add Year as a parameter and then the function will work next year without edit.
You can expand the function if you want.

I show 2 examples below

Lambda DGP.xlsm
	B
1
2	5-Sep-23 4:22 PM
3	5-Sep-23 8:05 PM
2e

Cell Formulas
Range	Formula
B2	B2	=DateTime(5,9,16,22)
B3	B3	=DateTime(5,9,20,5)

With Name Manager
new DateTime
value =LAMBDA(Day,Month,hour,minute,LET(d,Day,mo,Month,h,hour,m,minute,Text(DATE(2023,mo,d)+TIME(h,m,0),"d-mmm-yy h:mm Am/PM")))

 

[Dave Patton]

The function can be expanded to consider multiple actions such as Approved, Not Approved, Reviewed, etc and it could
consider multiple Authorized people.

The formula can reference cells or it can be self-contained.

Lambda DGP.xlsm
	A	B	C	D
16		Approved by gymwrecker 9-May-23 12:25 PM
17
18		Approved by gymwrecker 10-May-23 10:15 AM
2e

Cell Formulas
Range	Formula
B16	B16	=DateTime2(9,5,12,25)
B18	B18	=Stamp(10,5,10,15)

In Name Manager
DateTime2 =LAMBDA(Day,Month,hour,minute,LET(d,Day,mo,Month,h,hour,m,minute,!$B$4&" by "&!$B$5& " "&TEXT(DATE(2023,mo,d)+TIME(h,m,0),"d-mmm-yy h:mm Am/PM")))

Stamp
=LAMBDA(Day,Month,hour,minute,LET(d,Day,mo,Month,h,hour,m,minute, "Approved by gymwrecker "&TEXT(DATE(2023,mo,d)+TIME(h,m,0),"d-mmm-yy h:mm Am/PM")))

 

[Dave Patton]

Lambda DGP.xlsm
	A	B	C	D	E	F	G
12		Approved By: gymwrecker 17-Mar-23 3:15 PM
2e

Cell Formulas
Range	Formula
B12	B12	=Stamp(17,3,15,15)

Minor edit to the new function "Stamp"
=LAMBDA(Day,Month,Hour,Minute,LET(d, Day, mo, Month, h, Hour, m, Minute, "Approved By: gymwrecker " & TEXT(DATE(2023, mo, d) + TIME(h, m, 0), "d-mmm-yy h:mm Am/PM")))

N.B. Next year, you will have to edit the year part 2023 to 2024. It could be a new function say Stamp24

 

[gymwrecker]

Dave Patton, the LAMBDA formula is working for me. Following your example on Post #36, B12 =STAMP(17,3,1515), does this means I'd have to edit the formula in B12 when entering data at different dates and times?

 

[Dave Patton]

"Following your example on Post #36, B12 =STAMP(17,3,1515)"

Please check your data entry, the quote above is missing a comma. It should be =STAMP(17,3,15,15)

For a different date and time, you enter or edit the formula with the necessary parameters.

Lambda DGP.xlsm
	A	B	C	D
12		Approved By: gymwrecker 17-Mar-23 3:15 PM
13		Approved By: gymwrecker 18-Mar-23 3:20 PM
14		Approved By: gymwrecker 10-May-23 3:20 PM
15
2e

Cell Formulas
Range	Formula
B12	B12	=Stamp(17,3,15,15)
B13	B13	=Stamp(18,3,15,20)
B14	B14	=Stamp(10,5,15,20)

 

[gymwrecker]

That's what I thought, just wanted to make sure! Thank you!

If I need to expand the function to another action, i.e. Recommend Approval, I would need to create a new LAMBDA and replace "Approved By" with "Recommend Approval", and create a new formula, i.e., =Stamp2, right?

 

[Dave Patton]

you can create a new function or

revise the function to allow a variety of actions such as Recommend Approval, Approved, Not Approved

Please try the function and consider carefully what you require and then advise.
see previous comment
The function can be expanded to consider multiple actions such as Approved, Not Approved, Reviewed, etc and it could
consider multiple Authorized people.

The formula can reference cells or it can be self-contained.