CS question on the best way to perform a spreadsheet calculation

[Jofwu]

I've got an optimization question that's come up while working on my Stormlight timeline. I'm working in Google Sheets. I need to do a particular calculation a LOT, and with more books it will only happen more. I'd like to do it the most efficient way possible, so that the spreadsheet takes less time to load and process changes.

Rosharan dates follow the format YYYY-MM-WW-D, with 5 days/week, 10 weeks/month, and 10 months/year. In my spreadsheet I'm using parenthesis, (YYYY-MM-WW-D), to indicate dates prior to 1-1-1-1.

I need to be able to enter dates in this format, and I need to write calculation results in this format (as a string). But doing calculations is obviously best done with a single number in days. I use 1-1-1-1 as day 1. So if I need to do a calculation, like 1173-8-4-3 + 22 days, I convert the date string to number of days (1173*500 + 8*50 + 4*5 + 3), add 22, then convert back to a formatted strong.

Spreadsheets aren't great for parsing strings as far as I'm aware, so the first step is a messy process of looking for each hyphen and pulling out the numbers between. And putting the results back is a garble of rounddown() and mod() functions.

It works fine, but I'm curious if there's a better way to do this with a lookup table. Every possible date over thousands of years isn't logical, but I could do this for the 500 days in a year. List each date from 1-1-1 to 10-10-5 and pair them with 1 to 500. Now I would just have to parse the year, multiply it by 500, look for the rest of the date in my table, get the number of days, and add it. Going the other way is similar. I get the years like usual (rounddown x/500), then lookup mod(x, 500) in the table and mash the strings together.

So... Is there any way to figure if this method should be faster before converting my entire spreadsheet and actually testing?

[Bugsy]

19 minutes ago, Ookla the Jof said:

I've got an optimization question that's come up while working on my Stormlight timeline. I'm working in Google Sheets. I need to do a particular calculation a LOT, and with more books it will only happen more. I'd like to do it the most efficient way possible, so that the spreadsheet takes less time to load and process changes.

Rosharan dates follow the format YYYY-MM-WW-D, with 5 days/week, 10 weeks/month, and 10 months/year. In my spreadsheet I'm using parenthesis, (YYYY-MM-WW-D), to indicate dates prior to 1-1-1-1.

I need to be able to enter dates in this format, and I need to write calculation results in this format (as a string). But doing calculations is obviously best done with a single number in days. I use 1-1-1-1 as day 1. So if I need to do a calculation, like 1173-8-4-3 + 22 days, I convert the date string to number of days (1173*500 + 8*50 + 4*5 + 3), add 22, then convert back to a formatted strong.

Spreadsheets aren't great for parsing strings as far as I'm aware, so the first step is a messy process of looking for each hyphen and pulling out the numbers between. And putting the results back is a garble of rounddown() and mod() functions.

It works fine, but I'm curious if there's a better way to do this with a lookup table. Every possible date over thousands of years isn't logical, but I could do this for the 500 days in a year. List each date from 1-1-1 to 10-10-5 and pair them with 1 to 500. Now I would just have to parse the year, multiply it by 500, look for the rest of the date in my table, get the number of days, and add it. Going the other way is similar. I get the years like usual (rounddown x/500), then lookup mod(x, 500) in the table and mash the strings together.

So... Is there any way to figure if this method should be faster before converting my entire spreadsheet and actually testing?

I can't help much with sheets, because the scripting language for that is JS, which I have had... poor experiences with in the past. That said, I can help with the principles in general. What I'd start by doing is, as opposed to storing as a string in that format which will be a pain to parse, store YYYY in one column, MM in the next, WW in the one after, and D in the final one. Then add a final column which contains a boolean value indicating whether the given date is or is not before 1-1-1-1. Afterwards, you could automate the addition of any number of days, weeks, or months by using Math.floor() (the equivalent to your "rounddown()") and % (the equivalent to your "mod()"). This would be done in the Sheets scripting interface, and you would ultimately be able to simply use defined functions such as addDays(numOfDays) to calculate the new string. That way, you do the dirty work once and afterward you can simply use a function you've already made.

Edit: If necessary, I'd be willing to help where I can as you try to do this, assuming you take my suggestion. Contacting me on the discord (username is Bugsy) would likely be the fastest way. Good luck!  

Edited December 9, 2017 by Ookla The Red

[+Hoiditthroughthegrapevine]

Another possible implementation of this would be to create a J Srcipt object that is a Date Object. You could have properties of Years, Months, Weeks and Day, and could implement object level functions that convert one Date Object to another (or have methods that add dates to get a new date).

I don't know how google docs work, but if this was something you were trying to do in Excel a pretty basic VBA script could accomplish the date modifications. If this is something that is a serious time suck for you, let me know and I can probably develop a simple VB script that can do the calculation for you (you would fill out a VBA form with the details of the date that is known, and then fill out +X number of years,weeks, days, months and it would output the date formatted like you want it).