Google Sheets error: "The DATEVALUE parameter "April 5, 2023 " cannot be parsed as a date/time."

Hi @agustina!

By the sounds of it, this might be an error in Google Sheets directly, and not related to Zapier (especially since you’re using a formula that references cells).

If you add things directly in Google Sheets, do you still get these errors? 

NOTE: To help me understand what that formula is doing, I ran it through ChatGPT and received this:

This formula in Google Sheets performs two functions:

1. "=FECHANUMERO(DERECHA(B20;LARGO(B20)-ENCONTRAR(",";B20)-1))":

• DERECHA(B20;LARGO(B20)-ENCONTRAR(",";B20)-1) extracts the characters to the right of cell B20 until it finds a comma "," and subtracts 1 from the total length of cell B20.
• FECHANUMERO converts the result obtained above into a date value in numeric format.

2. "HORANUMERO(IZQUIERDA(B20;5))":

• IZQUIERDA(B20;5) extracts the first 5 characters of cell B20, assuming it represents a time in text format.
• HORANUMERO converts the result obtained above into a time value in numeric format.

Finally, both functions are summed together to obtain the final result, which could be a combined date and time value in numeric format. It's important to note that the output may depend on the content of cell B20 and the date/time format used in the system. It may require adjustments based on the specific date and time formatting requirements.

Does that description seem accurate?

Hey there, @agustina! Thanks for those details!
You could try using Formatter by Zapier to get the date formatted correctly. It has a Date/Time feature that sounds like it may be what you’re looking for.

Full transparency though, this requires at least a Starter plan to access Formatter and multi-step zaps.

If you do decide to go that route here’s a few more resources:

• https://help.zapier.com/hc/en-us/articles/8496257974029-Modify-date-and-time-formats-in-Zaps
• https://help.zapier.com/hc/en-us/articles/8496307860109-Adjust-date-and-time-values-in-Zaps
• https://zapier.com/blog/format-datetimes/

I hope some of this helps! 

Hi @agustina 

Hmm, that’s an odd one. Is there another Calendly field that you could use? I can see that the field is called ‘start time pretty’, ‘pretty’ is generally software speak for writing like a human. Calendly has a field called ‘Start time’ that will give you a format similar to this: 2023-04-06T10:00:00-06:00 That field will work better in the Formatter step. 

When you’re working with time/date fields it’s also important to check the timezone for the Zap, and for each of the apps that you’re using. You can find the timezone in the Zap settings:

I hope that helps, let us know how you get on!

Huh, that’s interesting. 🤔

In the Formatter step, is the “from format” dropdown filled out?

I’m wondering if it’s reading the incoming date wrong and so the output is wonky.

Regarding the spreadsheet, Google Sheets is super particular about formatting. Usually when the row is being inserted at the top this because the first column is hidden or missing a header. Usually making column A visible and/or adding a header fixes it.

Could you give that a shot? 🤞🏽 Be sure to turn your zap off while making any changes in the spreadsheet as that can also cause some weird behavior in the zap. 🙂

That field explicitly tells the Formatter step what the incoming date/time format is juust in case it gets it wrong. 🙂

Can you click the dropdown and select whichever one matches the incoming Calendly date/time? It’ll probably look something like: YYYY-MM-DDT00:00:00-00:00