Anyone know an Excel formula to convert Excel Date/Time to a Time-stamp?
Asked by
ETpro (
34605
)
October 26th, 2013
I could use the UNIX time code, but since that runs out of headroom in 32 bit code in 2038, I’d prefer to use a conversion using a 64 bit code. Any Visual Basic programmers that can give me a formula that will work in Excel 2007?
I have a bunch of product reviews. The reviews are on a remote review service site, and I can download a CSV file of them from there. I want to add Schema.org Rich Snippets about reviews, reviewer name, review date, star rating, etc. to help SEO for Google and Bing.
Many apply to the same product ID. But to store them in a database, I have to give each one its own unique object ID yet keep that object ID somehow related to the ID of the item reviewed. I want to come up with some scheme for sticking a unique suffix on the reviewed item ID, because no two lines in the spreadsheet can have duplicate object IDs.
If timestamping is out of the question, is there a formula that would find duplicate Reviewed-item IDs and build a unique Review ID using the Reviewed-item ID + a sequential number suffix, or a row-number or cell-number suffix?
I’m not concerned with the method. The ideas mentioned are just food for thought. If there is an easier way, I’m all ears. Whatever’s the simplest way to get unique Review IDs is what I’m looking for.
Observing members:
0
Composing members:
0
7 Answers
Any reason why it has to be a timecode? Could it be a GUID?http://stackoverflow.com/questions/7031347/how-can-i-generate-guids-in-excel
So long as it’s a unique ID, that’s sufficient. Thanks.
I see an issue with using Excel live formulas, because results can change, for example if they are based on time or on row numbers and the sheet gets sorted.
A couple of thoughts
1)
I would use a macro with code from @elbanditoroso‘s link to write down a GUID in the Object_ID column. The macro could write down an Object_ID where Object_ID is blank and leave existing values alone.
2)
Instead of Excel, store the list in Microsoft Access, Libre Office Base. or some other database. Have the database calculate the Object_ID whenever new rows are added to the table.
WOW. Found this. =(A2-DATE(1970,1,1))*86400
It produces accurate UNIX timestamp in Excel. Credit to Filip Czaja and his blog post here.
This is impervious to sorting, because it is based on a formula that rides with the row, and the actual date the review was written, as downloaded in a CSV from the StarProductReviews.com site. I particularly like it because concatenating the ID of the product reviewed with a separator like a dash character, and a suffix of the review date timestamp gives me a Review ID I can sort on and get all reviews grouped by the product the apply to, and in the order of the date they were reviewed. Too cool.
BTW, I can’t just use the human readable date (like 10/22/2010 14:07) that StarProductReviews exports because it has to be uploaded to a Yahoo! Store, and they do not allow special characters such as / and : in object IDs.
Simple. Pretty cool!
I guess the only concern is two reviews for the same product having exactly the same entry time.
@jaytkay With a few reviews coming in per 24 hour day and 2,200 products on the site, it’s a chance I’m willing to take. I couldn’t even spot a place in the reviews database where the same product ID got reviewed twice in the same day, much less the same hour and minute.
Answer this question 
This question is in the General Section. Responses must be helpful and on-topic.
Composing members:
0
