I developed an excel spreadsheet that calculates the GPS week and Epoch.?ÿ It appears to coincide with the NGS GPS week calendar, but does not use GPS time, just weeks from January 06, 1980 to any given date.?ÿ It is also not calculating seconds and not accounting for the leap second.?ÿ Though my GPS week calculation from excel appear correct, minus seconds not used for my purposes, should I be concerned about not using GPS time to calculate the GPS week from?
I believe the NGS GPS week calendar is based on code developed by Dr. Benjamin W. Remondi (in C) see: https://www.ngs.noaa.gov/gps-toolbox/bwr-c.txt ?ÿ?ÿ
You will notice it used Julian and modified Julian dates. I have clipped out the relevant portion of the code in the image below. (Taken from the above link).
If you use UTC instead of GPS time there is a window of several seconds (18?) around UTC midnight Saturday/Sunday (late afternoon or evening Saturday in the US) where you will get the wrong week. Otherwise it will be correct.
I'd worry more about checking leap year days are correct.
Thanks for your thoughts on this subject.?ÿ My excel spreadsheet just subtracts the dates to determine total days, then divides by 7 to get total weeks.?ÿ I assume excel keeps track of the leap years/days within its time clock.?ÿ The Epoch calculation is using the excel Year function.?ÿ The seconds seem like an odd quantity, I believe it is the elapsed number of seconds in the week of the calculated week number.?ÿ I assume that starts on day 1 (Sunday) at midnight?
Looking at your original post, you indicate that your spreadsheet determines the GPS Week and EPOCH. What do you mean by Epoch.?ÿ
GPS time is measured continuous and does not account for leap seconds.
As you have the NGS GPS Calendar you can check your spreadsheet results. You will likely find some discrepancies. I did using an approach similar to yours.Dr Remondi??s code works.
?ÿ
Thank you, I will look into Dr. Remondi's code a lot closer.?ÿ My spreadsheet calculates the GPS week, and a separate line calculation for the Epoch as year and fraction of year, which is the easiest part.?ÿ I was concerned with the GPS week, and if using excel would be good to calculate the week since I am not the calculating the seconds part of the GPS week, I assume excel tracks leap days.?ÿ But the correct approach may be to calculate the correct seconds and then week from that.?ÿ However, I will look into Dr. Remodi's code, that may reveal some of the confirmation I am looking for regarding approach.?ÿ But if I choose to calculate seconds as a learning exercise, it seems I should just have to calculate the elapsed number of seconds within the GPS week, which excel should be able to do easily...I may be over simplifying.
