Best way to bulk format date?

Questions
1

Hello there !

I have multiple values returns by HubSpot node that are in unix format :

image
image211×112 4.37 KB

I would need to convert all those dates on regular format date like MM/DD/YYYY I’ve been able to do it manually for one date using the Date/Time module but what if I have 20 different dates ?

Maybe I am missing something, thanks !

2

{{DateTime.fromMillis(1616344794000).toFormat(‘dd/MM/yyyy’)}}
put this in set node

for your event - put parameter without {{}} brackets
DateTime.fromMillis($json.hs_date_entered_customer).toFormat(‘dd/MM/yyyy’)

Play around.

fromMillis because your paramerer in timestamp in milliseconds format. (if seconds, then {{DateTime.fromSeconds(time).toFormat(‘yyyy-MM-dd’)}} )

3

Thanks for the reply !
So I wanted to first try with only one value using the Date/Time module.
The first thing is that the module will only return the data IF it contains a date :

image
image1302×647 25.4 KB

I’ll sometimes have objects without hs_date_entered_customer and still need them to be append on the sheets.

image
image375×876 12.4 KB

Thanks !

4

sort via if node
true if time exist. if exist then two one go to next,second format time and put merge 1 from formatted time, 2 from original workflow.

false if time not exist. if not exist - go to next

use merge node for append

{{DateTime.fromMillis(1616344794000).toFormat('dd/MM/yyyy')}}
not 
{{DateTime.fromMillis(1616344794000).toFormat(‘dd/MM/yyyy’)}}
error in 'dd/MM/yyyy'

Thinking just like this

or you can use your original workflow. Set node append new data by default.

5

Ok i’m starting to understand a few things !

So to actually avoid set node etc. Would it be possible to do this directly in the “Append data” node, because it’s using an expression ?

Like instead of :

{{ $node.HubSpot.json.properties.hs_date_entered_customer.value }}

Something like :

{{DateTime.fromMillis($node.HubSpot.json.properties.hs_date_entered_customer).toFormat('dd/MM/yyyy')}}

image
image371×885 25.9 KB

But this code returns a blank cell, even if I add .value
Maybe the formula is wrong or a string is returned instead of an integer ?

Thanks !

6

How do I know what your input structure is?

You just insert the desired time value without {{ }}

Set node to check if the time output is correct for you then. I showed you everything I could, the rest is up to you.

7

image
image2025×644 42.6 KB

This is my “set” node.
It works fine with a manual value
I’m able to display a value using a variable
But formula + variable returned undefined

8 
try to 
{{DateTime.fromMillis($node.HubSpot.json.properties.hs_date_entered_customer.value).toFormat('dd/MM/yyyy')}}
or
{{DateTime.fromMillis($json.properties.hs_date_entered_customer.value).toFormat('dd/MM/yyyy')}}
or
{{DateTime.fromMillis(Number($json.properties.hs_date_entered_customer.value)).toFormat('dd/MM/yyyy')}}
or
{{DateTime.fromMillis(Number($json["properties"]["hs_date_entered_customer"]["value"])).toFormat('dd/MM/yyyy')}}

i don’t have any example data, so…

9

image
image2010×617 53.7 KB

Last two worked !
So it was indeed because it returned a string and not “numer” it seems.
That’s weird because I also tried with parseInt and it didn’t work, but now it does either with parseInt or Number.

I really appreciate your help !
Thanks a lot

10

It took me a long time to figure out how to output the time in SET node using luxon built-in commands without using DateTime

another one solved

11

Yeah, to be honest n8n could have been more verbose on this one and just tell be it expected int and not string

12

You use the old view (but also functional) in the standard (new) view clearly shows where the number and where the string

I got seriously stuck on it myself back in the day :smile:

Please like
mark the Topic as resolved

13

Just a heads up - I’ve attached this thread to an internal feature concept that would convey expected datatype of a parameter. Agree that this could be clearer in n8n.

14

Thank you that’s really awesome.