PDA

View Full Version : Solved: Now Function - convert to General format ?

bdsii
02-02-2010, 03:16 PM
I am working on a userform that will uses a TextBox to store a Transaction Number which would be a unique number. I would like to use the Now function and convert it to the General format so that it is a number based on the date and time the userform was initialized. The converted number could contain a decimal for the Transaction Number and that would be OK. I wanted to use the Now function because if there were any questions about the date and time of the entry it could be gained by converting the Transaction Number back to the date/time format. This was preferrable to code generating a unique Transaction Code.

Inside an excel spreadsheet I can use the Now function and then convert it to General format and it provides a number as I had hoped. I used the record function to help with me with this but for some reason I cannot get it to work within the userform.

I have researched and tried various code to format it but have not had any luck. I have tried both of the segments below and get back the info in the Date/Time format and not numeric. I figure this should be simple but it has given me fits!

Trans is the Textbox name in the userform

Trans = Now()
Me.Trans.Value = format(Now, General)

---- AND ---------

Trans = Now()
Me.Trans.Value = format(Me.Trans.Value, General)

Anybody see where I am going wrong with this ?

thanks !

xld
02-02-2010, 04:43 PM
Try

Me.Trans.Value = CDbl(Now)

bdsii
02-03-2010, 06:09 AM
Thanks xld ! Works as I had hoped. However, I am not sure I would have ever come to this solution. It gives me some research to do on this function. :-)

While we are at it, within a userform, how would this number be converted back to Date and Time ?

Thanks!

xld
02-03-2010, 06:18 AM
Date and time are just numbers in Excel, so effectively it already is date and time. To see it as such, use

Format(Me.Trans.Value, "dd/mm/yy hh:mm:ss")

bdsii
02-03-2010, 07:44 AM
Great! Marking it solved! thanks so much :-)