Need some assistance creating a spreadsheet formula to convert
from GPS seconds example: 245891.679754 to?ÿ Standard timestamp example: 2019/03/12 20:18:04.600?ÿ
Edit: try this as a start.?ÿ The constant may not be exactly right, as I can't get Excel to display your format including decimal seconds, and?ÿ week 0 seconds 0 gives the wrong starting point for GPS time.
=B3*7+(C3+D3)/86400+$F$3?ÿ?ÿ where the cells are respectively
B3 = GPS week, C3 = GPS seconds, D3 = Leap Seconds, F3 = 29225.99970972510
?ÿ
?ÿ
It's confusing to check the formula using the GPS origin because Excel doesn't know about leap seconds. By our present UTC clock, GPS origin was 18 seconds earlier than the UTC clock said at that time.
Maybe the constant is 29226, the number of days between Excel's starting point and GPS starting point.?ÿ But that doesn't make your supplied values match.
Look at NGS toolkit page for code by BWRemondi if you understand C coding. He uses pointers extensively.
I don't know why this is so hard for me to think about. Makes me feel stupid to have to post so many times with corrections.
Try constant 29226.00020833330000 which is the GPS time origin plus 18 seconds converted to Excel days.
In my formula above, either use -18 for leap seconds or change the sign in the formula.
This site may provide checking values: http://www.csgnetwork.com/gpstimeconv.html
I don't check with the values you supplied. I get GPS time 245891 to be UTC 20:18:11 (watch out for Excel rounding of fractional seconds) and if I use the converter in the link, which takes only whole seconds, week 2044 Tuesday 20:18:04 converts to 245884, not 245891, which matches my difference of 7 seconds.
Need some assistance creating a spreadsheet formula to convert
from GPS seconds example: 245891.679754 to?ÿ Standard timestamp example: 2019/03/12 20:18:04.600?ÿ
I'm glad you posted this! I've been discussing it with a co-worker and my wife, who happens to be an expert at Excel. I'm curious, how are you going to apply this?
Thanks for posting this! I looked over the source code for the time algorithms. I found the lines addressing leap seconds.
The time algorithms source code defines two constants. One for January 6, 1980. The other for January 1, 1901. I'm unfamiliar with the significance of the later.
Ok, drop all references to leap seconds. They just made past weeks longer than Excel knows about, so we don't need to consider them in any formula unless we are spanning a leap second in a session (probably doesn't work anyway). I was trying to hard to force them in.
Then the constant is 29226.
I have an extensive Excel spreadsheet created by others that I'm having trouble with. Wonder if you could help me figure out why it fails. The spreadsheet converts GPS epochs to Standard time, in an effort to average drone positioning between 5hz epochs.
Dataset can be https://www.dropbox.com/s/t9gc27bmecj3qgz/P4rtk_PPK_adjust.zip?dl= 0" target="true" rel="noopener">downloaded here https://www.dropbox.com/s/t9gc27bmecj3qgz/P4rtk_PPK_adjust.zip?dl=0
Hi...also referred to as GPS Seconds. See the Timekeeping section here. I can calculate UTC in Excel (GPS Seconds/60/60/24+29226), then format to date/time, but trying to avoid using Excel-will be working with a large amount of data.
If you intended to provide a link with your statement “See the Timekeeping section here.” It did not work.
If interested in writing a program, a previous reply in this thread https://www.ngs.noaa.gov/gps-toolbox/bwr-c.txt provides a link to C and FORTRAN code addressing all manner of time conversions used in GPS.
As GPS time consists of GPS week plus seconds of week, you need both to convert to UTC. Dr Remondi’s code linked above uses MJDs for the start of the 20th century and the start of GPS time. The code is pretty clear. It does use a lot of pointers.
HTH,
DMM
