Capturing 5 digits after a decimal 3

[LGMan]

Hi an odd request perhaps, I need to create a unique serial number within a file that never gets saved. I've had to create an Excel based application that users answer various data validations. Rather than save the workbook, I strip out all of their prompt answers and send a .csv file to a SharePoint site where that data gets picked up as a set of instructions for another team. So my 'base' file closes without saving, so having a simple count that increases with file open wont work.

I have settled on creating a serial number by joining 10 digits of the the =Now() function concatenated to the Employee id of the person who is answering the data validations. I figure then this approach will therefore create a unique serial number. Since I take a hard copy of the =Now() result at file open, and by the time that the user has completed all the data validations, then well, time will have moved on by at least a few if not more minutes.

So the =Now() function when formatted as a number is currently a 5.10 format, in that there are 10 decimals, a decimal point and 5 numbers to the left of the decimal. It will be many years before there are more than 5 digits to the left of the decimal, so I don't think I'll be around for anyone to complain to when time trips over to 6 digits

I want to gather 5 digits before the decimal (=Left(B45,5) so no problem there, but.....
how do I get the first 5 digits after the decimal?

Or is there another approach that I havent thought of?
Many Thanks

 

[SkipVought]

hi,

Now is a NUMBER not a string!

Dim MyNumber as Single

MyNumber = Now - Int(Now) 

MyNumber = Int(MyNumber * 10^5) / 10^5

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

I want to gather 5 digits before the decimal

why 5?
What if there are 6 or 4, for instance?

[highlight #FCE94F]This is NUMERIC stuff[/highlight]

MsgBox Int(Now)

So the =Now() function when formatted as a number is currently a 5.10 format

Whatever FORMAT you imposed on this value is IMMATERIAL. It is a NUMBER of whatever significant digits, and may even be an irational number. Your FORMAT is like looking at the sunset with shades. It does not change the sunset.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MajP]

=TEXT(B45,"00000.00000")

 

[SkipVought]

I should have read the OP more carefully.

MajP has your answer to concatenate to your EmployeeID. ==> *

If you use a formula on a sheet...
[tt]
=TEXT(Now(),"00000.00000")
[/tt]

If in VBA...

Dim sAppend as string

sAppend =Format(Now, "00000.00000")

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Thanks. both for your relpies. Majp, your option gives me a result that I can futher use a =left(cell_ref, 5) function.

Skip, i like your sunset quote , I've used a number format on =Now() and it always looks to have at most 10 digits after the decimal, hence my 5.10 (which was a crude way of trying the describe the format of the number), granted that these 10 may be all zeros for a milli-second, however the 5 digit increases in what appears to be single seconds.
A number like 41732.87366 plus the employee_id is going to appear as my serial number of 4173287366-60237A for a split second and in my mind will never get repeated.

Do you have a better way of creating a unique number

 

[SkipVought]

One swell foop!
[tt]
=TEXT(NOW()*10^5,"0000000000")
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MajP]

however the 5 digit increases in what appears to be single seconds

Not seconds. The decimal portion is the fraction of a day that has elapsed. So five places would be be 1/100000 th of a day. Since a day is 86400 seconds it is more like .86 seconds.

so the decimal xxxxx.5000000 is 12:00pm
and xxxxx.75 is 6:00 pm

 

[LGMan]

Ah brill, a star for both of you

 

[LGMan]

Ok, thanks 0.86 seconds is nearly a second when manually counting by the one thousand, two thousand method. ;-).
So by going as far as the 5th digit is quick enough in this instance to give me a unique number (coupled to the employee_id)

by using =TEXT(NOW()*10^5,"0000000000") and performing a copy pastespecial at workbook open, seems simple enough, but as always if there is an easier way to create a unique number then.....

 

[MajP]

Formatting a timestamp like you are doing is a pretty common method, and pretty simple. So this is as good as any method IMO.

 

[strongm]

>Ok, thanks 0.86 seconds is nearly a second

Actually, the VBA time, despite initial appearances to the contrary when you look at the underlying numeric representation as you are doing, is only accurate to the second.