I've looked around and have seen a lot of people ask this same question, but all of the answers I get are either confusing or don't seem to fit with my situation. Here's a dummy sheet (first time, just let me know if it needs anything else added): https://docs.google.com/spreadsheets/d/1Lb7RQy1mgFlMsxH_mayGs8sN8hkj5ALt6xGorzZnaIo/edit?usp=sharing The function that I created via script editing is called TEST2 which just indexes the input. The problem is, the input is a range of data where that data is time entries. So whenever I use TEST2 on the time column, the index it returns should be 0:00:00, but it's actually 12/30/1899. Do I need to handle this via the custom function in the script, or is there another way?
-
2Adding to what Rubén posted below, the reason you get the "odd" date of December 30, 1899 is that this date is "ground zero" as far as Google Sheets time marking is concerned. That is, all dates are actually marked from that date. For instance, Google Sheets sees June 30, 2021 as 44377 — or 44377 days since December 30, 1899. Times are then fractions; so noon on June 30, 2021 is seen as 44377.5 and so on. Since all you have is a duration, it would be a fraction less than 1, which defaults to 0. And if your format is set to date... well, you get December 30, 1899.– Erik TylerCommented Jun 30, 2021 at 16:50
-
See this answer for an explanation of how date and time values work in spreadsheets.– doubleunaryCommented Jun 30, 2021 at 17:54
Add a comment
|
1 Answer
The "problem" occurs because the cell format of the cell containing the custom function.
One way to solve this is to change the format from date to duration.
Related
answered Jun 30, 2021 at 15:52
Rubén - Volunteer Moderator -♦Rubén - Volunteer Moderator -
46.1k18 gold badges100 silver badges297 bronze badges