Google-Apps-Script - convert HH:MM argument to fraction of a day
I have '12:30' in a cell in my Google sheet. It is passed in as an argument into my function (Google Apps Script) and I want to be able to extract the value 12 and the value 30. I want to then be able to return 0.5+0.0208333 = 0.5208333 to represent the fraction of the day that 12:30 represents.
I have searched and not been able to find a solution to this simple problem.
When I change the format to 'number' on the Google sheet it is passed correctly as a float 0.520833 but that defeats the purpose of being able to allow the user to enter a 'HH:MM' format value on the Google sheet user interface.
Can someone tell me how, specifically within Google Apps Script I can extract the HH and the MM passed in as an 'HH:MM' argument.
datetime google-apps-script time
edited Nov 12 '18 at 15:24
TheMaster
10.4k3835
asked Nov 12 '18 at 12:23
Purnendu NathPurnendu Nath
1
add a comment |
I have '12:30' in a cell in my Google sheet. It is passed in as an argument into my function (Google Apps Script) and I want to be able to extract the value 12 and the value 30. I want to then be able to return 0.5+0.0208333 = 0.5208333 to represent the fraction of the day that 12:30 represents.
I have searched and not been able to find a solution to this simple problem.
When I change the format to 'number' on the Google sheet it is passed correctly as a float 0.520833 but that defeats the purpose of being able to allow the user to enter a 'HH:MM' format value on the Google sheet user interface.
Can someone tell me how, specifically within Google Apps Script I can extract the HH and the MM passed in as an 'HH:MM' argument.
datetime google-apps-script time
edited Nov 12 '18 at 15:24
TheMaster
10.4k3835
asked Nov 12 '18 at 12:23
Purnendu NathPurnendu Nath
1
Take a look at the split methid in JavaScript
– Cooper
Nov 12 '18 at 16:46
Thanks Cooper but... when I use str.split(";") I get the following ERROR popping up on the Google sheet cell: >> TypeError: Cannot find function split in object it's as if the function split() and a lot of other normal Javascript functions don't exist within Google Apps Script! I am baffled by that too as a result!
– Purnendu Nath
Nov 13 '18 at 16:27
Oh so it’s probably being treated as a Date(). So try using those methods.
– Cooper
Nov 13 '18 at 22:23
Or perhaps toString().
– Cooper
Nov 13 '18 at 22:24
Thanks Cooper! Just doing a toString() as you suggested and subsequently running string functions allowed me to get the HH and MM as integer values. What you said about toString() was what helped me past the stumbling block and then it was smooth sailing. Cheers!
– Purnendu Nath
Nov 14 '18 at 11:22
add a comment |
I have '12:30' in a cell in my Google sheet. It is passed in as an argument into my function (Google Apps Script) and I want to be able to extract the value 12 and the value 30. I want to then be able to return 0.5+0.0208333 = 0.5208333 to represent the fraction of the day that 12:30 represents.
I have searched and not been able to find a solution to this simple problem.
When I change the format to 'number' on the Google sheet it is passed correctly as a float 0.520833 but that defeats the purpose of being able to allow the user to enter a 'HH:MM' format value on the Google sheet user interface.
Can someone tell me how, specifically within Google Apps Script I can extract the HH and the MM passed in as an 'HH:MM' argument.
datetime google-apps-script time
edited Nov 12 '18 at 15:24
TheMaster
10.4k3835
asked Nov 12 '18 at 12:23
Purnendu NathPurnendu Nath
1
I have '12:30' in a cell in my Google sheet. It is passed in as an argument into my function (Google Apps Script) and I want to be able to extract the value 12 and the value 30. I want to then be able to return 0.5+0.0208333 = 0.5208333 to represent the fraction of the day that 12:30 represents.
I have searched and not been able to find a solution to this simple problem.
When I change the format to 'number' on the Google sheet it is passed correctly as a float 0.520833 but that defeats the purpose of being able to allow the user to enter a 'HH:MM' format value on the Google sheet user interface.
Can someone tell me how, specifically within Google Apps Script I can extract the HH and the MM passed in as an 'HH:MM' argument.
datetime google-apps-script time
datetime google-apps-script time
edited Nov 12 '18 at 15:24
TheMaster
10.4k3835
asked Nov 12 '18 at 12:23
Purnendu NathPurnendu Nath
1
edited Nov 12 '18 at 15:24
TheMaster
10.4k3835
asked Nov 12 '18 at 12:23
Purnendu NathPurnendu Nath
1
edited Nov 12 '18 at 15:24
TheMaster
10.4k3835
edited Nov 12 '18 at 15:24
TheMaster
10.4k3835
edited Nov 12 '18 at 15:24
TheMaster
10.4k3835
10.4k3835
asked Nov 12 '18 at 12:23
Purnendu NathPurnendu Nath
1
asked Nov 12 '18 at 12:23
Purnendu NathPurnendu Nath
1
asked Nov 12 '18 at 12:23
Purnendu NathPurnendu Nath
1
1
Take a look at the split methid in JavaScript
– Cooper
Nov 12 '18 at 16:46
Thanks Cooper but... when I use str.split(";") I get the following ERROR popping up on the Google sheet cell: >> TypeError: Cannot find function split in object it's as if the function split() and a lot of other normal Javascript functions don't exist within Google Apps Script! I am baffled by that too as a result!
– Purnendu Nath
Nov 13 '18 at 16:27
Oh so it’s probably being treated as a Date(). So try using those methods.
– Cooper
Nov 13 '18 at 22:23
Or perhaps toString().
– Cooper
Nov 13 '18 at 22:24
Thanks Cooper! Just doing a toString() as you suggested and subsequently running string functions allowed me to get the HH and MM as integer values. What you said about toString() was what helped me past the stumbling block and then it was smooth sailing. Cheers!
– Purnendu Nath
Nov 14 '18 at 11:22
add a comment |
Take a look at the split methid in JavaScript
– Cooper
Nov 12 '18 at 16:46
Thanks Cooper but... when I use str.split(";") I get the following ERROR popping up on the Google sheet cell: >> TypeError: Cannot find function split in object it's as if the function split() and a lot of other normal Javascript functions don't exist within Google Apps Script! I am baffled by that too as a result!
– Purnendu Nath
Nov 13 '18 at 16:27
Oh so it’s probably being treated as a Date(). So try using those methods.
– Cooper
Nov 13 '18 at 22:23
Or perhaps toString().
– Cooper
Nov 13 '18 at 22:24
Thanks Cooper! Just doing a toString() as you suggested and subsequently running string functions allowed me to get the HH and MM as integer values. What you said about toString() was what helped me past the stumbling block and then it was smooth sailing. Cheers!
– Purnendu Nath
Nov 14 '18 at 11:22
Take a look at the split methid in JavaScript
– Cooper
Nov 12 '18 at 16:46
Take a look at the split methid in JavaScript
– Cooper
Nov 12 '18 at 16:46
Thanks Cooper but... when I use str.split(";") I get the following ERROR popping up on the Google sheet cell: >> TypeError: Cannot find function split in object it's as if the function split() and a lot of other normal Javascript functions don't exist within Google Apps Script! I am baffled by that too as a result!
– Purnendu Nath
Nov 13 '18 at 16:27
Thanks Cooper but... when I use str.split(";") I get the following ERROR popping up on the Google sheet cell: >> TypeError: Cannot find function split in object it's as if the function split() and a lot of other normal Javascript functions don't exist within Google Apps Script! I am baffled by that too as a result!
– Purnendu Nath
Nov 13 '18 at 16:27
Oh so it’s probably being treated as a Date(). So try using those methods.
– Cooper
Nov 13 '18 at 22:23
Oh so it’s probably being treated as a Date(). So try using those methods.
– Cooper
Nov 13 '18 at 22:23
Or perhaps toString().
– Cooper
Nov 13 '18 at 22:24
Or perhaps toString().
– Cooper
Nov 13 '18 at 22:24
Thanks Cooper! Just doing a toString() as you suggested and subsequently running string functions allowed me to get the HH and MM as integer values. What you said about toString() was what helped me past the stumbling block and then it was smooth sailing. Cheers!
– Purnendu Nath
Nov 14 '18 at 11:22
Thanks Cooper! Just doing a toString() as you suggested and subsequently running string functions allowed me to get the HH and MM as integer values. What you said about toString() was what helped me past the stumbling block and then it was smooth sailing. Cheers!
– Purnendu Nath
Nov 14 '18 at 11:22
add a comment |
1 Answer
1
active
oldest
votes
- Select the column(say A:A) in Google sheets and format it as
HH:MM. This will enable users to enter inHH:MMformat. - Use apps-script to change the number format onEdit
Sample Script:
function onEdit(e)
if (e.range.columnStart != 1) return; //A:A
e.range.setNumberFormat('#.######');
answered Nov 12 '18 at 15:18
TheMasterTheMaster
10.4k3835
Thanks but it does not come even close to answering the question I asked.
– Purnendu Nath
Nov 13 '18 at 16:21
@purn How and why?
– TheMaster
Nov 13 '18 at 16:26
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53262141%2fgoogle-apps-script-convert-hhmm-argument-to-fraction-of-a-day%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
- Select the column(say A:A) in Google sheets and format it as
HH:MM. This will enable users to enter inHH:MMformat. - Use apps-script to change the number format onEdit
Sample Script:
function onEdit(e)
if (e.range.columnStart != 1) return; //A:A
e.range.setNumberFormat('#.######');
answered Nov 12 '18 at 15:18
TheMasterTheMaster
10.4k3835
Thanks but it does not come even close to answering the question I asked.
– Purnendu Nath
Nov 13 '18 at 16:21
@purn How and why?
– TheMaster
Nov 13 '18 at 16:26
add a comment |
- Select the column(say A:A) in Google sheets and format it as
HH:MM. This will enable users to enter inHH:MMformat. - Use apps-script to change the number format onEdit
Sample Script:
function onEdit(e)
if (e.range.columnStart != 1) return; //A:A
e.range.setNumberFormat('#.######');
answered Nov 12 '18 at 15:18
TheMasterTheMaster
10.4k3835
Thanks but it does not come even close to answering the question I asked.
– Purnendu Nath
Nov 13 '18 at 16:21
@purn How and why?
– TheMaster
Nov 13 '18 at 16:26
add a comment |
- Select the column(say A:A) in Google sheets and format it as
HH:MM. This will enable users to enter inHH:MMformat. - Use apps-script to change the number format onEdit
Sample Script:
function onEdit(e)
if (e.range.columnStart != 1) return; //A:A
e.range.setNumberFormat('#.######');
answered Nov 12 '18 at 15:18
TheMasterTheMaster
10.4k3835
- Select the column(say A:A) in Google sheets and format it as
HH:MM. This will enable users to enter inHH:MMformat. - Use apps-script to change the number format onEdit
Sample Script:
function onEdit(e)
if (e.range.columnStart != 1) return; //A:A
e.range.setNumberFormat('#.######');
answered Nov 12 '18 at 15:18
TheMasterTheMaster
10.4k3835
answered Nov 12 '18 at 15:18
TheMasterTheMaster
10.4k3835
answered Nov 12 '18 at 15:18
TheMasterTheMaster
10.4k3835
answered Nov 12 '18 at 15:18
TheMasterTheMaster
10.4k3835
10.4k3835
Thanks but it does not come even close to answering the question I asked.
– Purnendu Nath
Nov 13 '18 at 16:21
@purn How and why?
– TheMaster
Nov 13 '18 at 16:26
add a comment |
Thanks but it does not come even close to answering the question I asked.
– Purnendu Nath
Nov 13 '18 at 16:21
@purn How and why?
– TheMaster
Nov 13 '18 at 16:26
Thanks but it does not come even close to answering the question I asked.
– Purnendu Nath
Nov 13 '18 at 16:21
Thanks but it does not come even close to answering the question I asked.
– Purnendu Nath
Nov 13 '18 at 16:21
@purn How and why?
– TheMaster
Nov 13 '18 at 16:26
@purn How and why?
– TheMaster
Nov 13 '18 at 16:26
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53262141%2fgoogle-apps-script-convert-hhmm-argument-to-fraction-of-a-day%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Take a look at the split methid in JavaScript
– Cooper
Nov 12 '18 at 16:46
Thanks Cooper but... when I use str.split(";") I get the following ERROR popping up on the Google sheet cell: >> TypeError: Cannot find function split in object it's as if the function split() and a lot of other normal Javascript functions don't exist within Google Apps Script! I am baffled by that too as a result!
– Purnendu Nath
Nov 13 '18 at 16:27
Oh so it’s probably being treated as a Date(). So try using those methods.
– Cooper
Nov 13 '18 at 22:23
Or perhaps toString().
– Cooper
Nov 13 '18 at 22:24
Thanks Cooper! Just doing a toString() as you suggested and subsequently running string functions allowed me to get the HH and MM as integer values. What you said about toString() was what helped me past the stumbling block and then it was smooth sailing. Cheers!
– Purnendu Nath
Nov 14 '18 at 11:22