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 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.

 

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

 

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

 

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

 

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