Window 10 Firefox login table date scientific number conversion to Excel date numbers
Dear Sir or Madam; I downloaded a login CSV file from the Firefox browser. The date columns format in the CSV login file is ‘1.64055E+12’. In EXCEL, 44556.79166 would translate to 2021/12/26 18:59:59. The translation of ‘1.64055E+12’ should not be the same exact date, but should be in the same date range, as the 44556.79166 number. I’m not a mathematician. Would you please show me how to convert the '1.64055E+12' value to an equivalent excel date/time number? I could then format the excel date number cells to an yyyy/mm/dd hh:mm:ss format. These scientific number values are in the Firefox login file CSV downloads so I assume they were intended to be used in EXCEL spread sheet. These values converted to EXCEL date values would help me sort account password date change records by account name and change date. I could review sorted account records (and thus determine the current password for individual old accounts). Your solution would help me immensely and I appreciate your time. Sincerely, Clyde Stewart – wes45505@aol.com, Boise, ID
Τροποποιήθηκε στις
Επιλεγμένη λύση
Note that you can easily convert an epoch string via a JavaScript bookmarklet in Firefox or via the Web Console. You can create a new bookmark and paste the JavaScript code in its Location field and give it a suitable name.
javascript:void(prompt('datetime',(new Date(Number(prompt('epoch',Date.now())))).toISOString()));
javascript:var d,DT=prompt('paste 13 digit epoch string',Date.now()); if(DT){ d=new Date(Number(DT)); prompt(d.toLocaleString('en-GB')+'\n'+d.toISOString()+'\n'+d.toUTCString(),d.toISOString()); } void(0);
Ανάγνωση απάντησης σε πλαίσιο 👍 0
Όλες οι απαντήσεις (7)
Firefox uses the Unix Epoch format to store datetime strings, so you need to convert this to the format Excel uses.
See:
- /questions/1335038 Deleting saved logins not used for 5 years
- https://www.google.com/search?sa=N&num=100&q=excel%20convert%20unix%20epoch
Dear Sir or Madam; Thank you for your input. I believe you are correct in the translation of Unix dates to corresponding EXCEL Dates. My problem is that I'm no longer versed in intermediate math. I'm very old. Anyway, My problem became how to translate a UNIX date in scientific format to a number.
Given: 1. The Scientific dates used in the Firefox login/pw table are in UNIX GMT milliseconds.
2. The offset between UNIX and EXCEL is 70 years. 3. EXCEL Day 1 is 1900/1/1 4. UNIX Day 1 is 1970/1/1 (We eventually need to add 70 years to make it an EXCEL date.)
Here's what I finally found:
A2: 1.640552253122E+12 Format: Scientific, 12 zeroes Actual value in excel: 1640552253122
B2: =((((A2/10^3)/60)/60)/24) + DATE(1970,1,1) Format: yyyy/dd/mm hh:mm:ss.000 Actual displayed value in EXCEL is: 2021/12/26 20:57:33.122
I had made the original task/problem more difficult than it had to be. The EPOCH CONVERTER made me realize I had more digits in the actual scientific formatted number. When I changed from 5 to 12 zeroes, I could see them.
See https://www.epochconverter.com
Τροποποιήθηκε στις
A2: 1.640552253122E+12 Format: Scientific, 12 zeroes Actual value 1640552253122 B2: =((((A2/10^3)/60)/60)/24) + DATE(1970,1,1) Format: yyyy/mm/dd hh:mm:ss.000 Actual value displayed: 2021/12/26 20:57:33.122
Notes: 1. Firefox dates are in UNIX GMT millisecond format. 2. The offset between UNIX GMT Day 1 and EXCEL Day 1 is 70 years. 3. The UNIUX Day 1 is 1970/1/1 and the EXCEL Day 1 is 1900/1/1. 4. An EPOCH CONVERTER at https://www.epochconverter.com was a useful tool.
For now I have to figure out how to highlight the row with the most recent logon date within each group of sites (location URLs where I logged on. There are hundreds of those records. I would like to delete all but the most recent logon record for each site. That would leave me records with the most current site, logon ID and logon password records. I'd keep this current password list on a flash drive off of the PC. Any ideas?
For now I will only by highlighting the 'keepers'.
Τροποποιήθηκε στις
I added my solution here twice but didn't save it properly. Problem solved.
640552253122E+12 Format: Scientific, 12 zeroes Actual value 1640552253122 B2: =((((A2/10^3)/60)/60)/24) + DATE(1970,1,1) Format: Custom, yyyy/mm/dd hh:mm:ss.000 Actual value displayed: 2021/12/26 20:57:33.122 (GMT)
Notes: 1. Firefox dates are in UNIX GMT millisecond format. 2. The offset between UNIX GMT Day 1 and EXCEL Day 1 is 70 years. 3. The UNIUX Day 1 is 1970/1/1 and the EXCEL Day 1 is 1900/1/1. 4. An EPOCH CONVERTER at https://www.epochconverter.com was a useful tool.
For now I have to figure out how to highlight the row with the most recent logon date within each group of sites (location URLs where I logged on. There are hundreds of those records. I would like to delete all but the most recent logon record for each site. That would leave me records with the most current site, logon ID and logon password records. I'd keep this current password list on a flash drive off of the PC. Any ideas?
For now I will only by highlighting the 'keepers'.
Τροποποιήθηκε στις
640552253122E+12 Format: Scientific, 12 zeroes Actual value 1640552253122 B2: =((((A2/10^3)/60)/60)/24) + DATE(1970,1,1) Format: Custom, yyyy/mm/dd hh:mm:ss.000 Actual value displayed: 2021/12/26 20:57:33.122 (GMT)
Notes: 1. Firefox dates are in UNIX GMT millisecond format. 2. The offset between UNIX GMT Day 1 and EXCEL Day 1 is 70 years. 3. The UNIUX Day 1 is 1970/1/1 and the EXCEL Day 1 is 1900/1/1. 4. An EPOCH CONVERTER at https://www.epochconverter.com was a useful tool.
For now I have to figure out how to highlight the row with the most recent logon date within each group of sites (location URLs where I logged on. There are hundreds of those records. I would like to delete all but the most recent logon record for each site. That would leave me records with the most current site, logon ID and logon password records. I'd keep this current password list on a flash drive off of the PC. Any ideas?
For now I will only by highlighting the 'keepers'.
Επιλεγμένη λύση
Note that you can easily convert an epoch string via a JavaScript bookmarklet in Firefox or via the Web Console. You can create a new bookmark and paste the JavaScript code in its Location field and give it a suitable name.
javascript:void(prompt('datetime',(new Date(Number(prompt('epoch',Date.now())))).toISOString()));
javascript:var d,DT=prompt('paste 13 digit epoch string',Date.now()); if(DT){ d=new Date(Number(DT)); prompt(d.toLocaleString('en-GB')+'\n'+d.toISOString()+'\n'+d.toUTCString(),d.toISOString()); } void(0);