Best answer

Format time (17:30:00) to number (1730)

  • 16 March 2023
  • 7 replies
  • 45 views

Userlevel 1

The Google Form returns the times in the format "17:30:00" in the Google Sheets answer sheet. So, I need to format this time to "1730" with Zapier.

If it is not possible to do it with "Formatter by Zapier", can it be done with Js (although it is not the desired option)?

How can I do this?

Thanks for your time :)

icon

Best answer by ken.a 17 March 2023, 01:11

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

7 replies

Userlevel 7
Badge +11

Hi @polmarza!

It should be possible to format the column that contains the times directly within Google Sheets.

To do that you’d select the column then go to Format > Number > Custom date and time

15d2ef839ae2198b67b5e82fa026270d.png
Then select the relevant options to create the custom format:
24270c59b988be05811f2b0db9f16ed5.png
Which should then display the times in the desired format:
1bb47b74b0e04f99d1e3b39284276a2c.png

Then they should come through to the Zap in the desired format:

d0d8f32d593751bd5658f8d04eb5f0dd.png
Which would save you needing to use a Formatter (Date / Time > Format) action to convert the time into HHmm format.

Want to give that a go and see if that does the trick?

 

Userlevel 1

Thank you very much! It has worked @SamB 😁

Userlevel 7
Badge +11

Yay! 🎉 Thanks for confirming that worked @polmarza. I’m so glad that sorted it! 😁

Userlevel 1

Sorry @SamB , it worked for Google Sheets, but when I went to search for the information from zapier, it still read the time in 17:30:00 format:


Then I tried to do it through Zapier. I think it's configured correctly, but the answer I get is not in 24h format... This is what happens when I click on "test":

 

But the result is 0530 instead of 1730. 

Can you think of a solution? 

Thanks a lot Sam

Userlevel 7
Badge +6

Hey @polmarza,

I’m glad the suggestion by SamB worked. That said, looking at the screenshot you sent, the “To Format” field seems picky when converting the time. What you want to do here is to replace the “hh” with “HH” and leave the “From Format” field blank. Here’s what it will look like:

Date / Time in Formatter Setup:

9f7622aeb18a601556f6fdb6e80b8378.png
(view larger)

Date / Time in Formatter Output:

7f0ce29d081d506bf6f0b1f569e8579e.png
(view larger)

Kindly give it a try and let me know how it goes?

Userlevel 1

Great! Now it's all worked out :)

Please @ken.a close the thread and mark your answer as correct ^^

Userlevel 7
Badge +11

Thanks for hopping back in here to let us know that solved it, @polmarza. I’m really pleased to see ken.a was able to help you with that Formatter action! 😁

Happy Zapping! ⚡