Solved: Now Function - convert to General format ?

[bdsii]

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

[VBA]
Trans = Now()
Me.Trans.Value = format(Now, General)
[/VBA]

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

[VBA]
Trans = Now()
Me.Trans.Value = format(Me.Trans.Value, General)
[/VBA]


Anybody see where I am going wrong with this ?


thanks !

[Bob Phillips]

Try

[vba]

Me.Trans.Value = CDbl(Now)
[/vba]

[bdsii]

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!

[Bob Phillips]

Date and time are just numbers in Excel, so effectively it already is date and time. To see it as such, use

[vba]

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

[bdsii]

Great! Marking it solved! thanks so much :-)