Best way to bulk format date?

[MoryCorp]

Hello there !

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

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 !

[lightcom]

{{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’)}} )

[MoryCorp]

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 :

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

Thanks !

[lightcom]

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

Merge - n8n Documentation

{{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.

[MoryCorp]

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')}}

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 !

[lightcom]

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.

[MoryCorp]

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

[lightcom]

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…

[MoryCorp]

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

[lightcom]

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

[MoryCorp]

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

[lightcom]

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

Please like
mark the Topic as resolved

[maxT]

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.

[lightcom]

Thank you that’s really awesome.