0

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?

2
  • 2
    Adding 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 Tyler
    Jun 30 at 16:50
  • See this answer for an explanation of how date and time values work in spreadsheets. Jun 30 at 17:54
1

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.