Date difference calculation returning strange results in google sheets

[Si]

Hello everyone, I have a simple question that I can not manage to answer for some reason.

I user one of the widely available methods to calculate the date difference from webhook data with: ​​formatDate​(Updated at​;X)​-​formatDate​(Started at​;X)​​ and this returns a result in miliseconds correctly (checked it 100 times).

I now want to simply add to it the calculation to return days as integer, which one would assume only needs correct division - BUT it is not working, the more I manipulate the number, the bigger it gets.

JSON data dates only:

{
"started_at": "2024-06-04T20:10:59.782Z",
"updated_at": "2024-07-05T19:49:01.599Z"
}

Pictures for reference:
This returns normal (first value) in miliseconds.

While this returns a strange number

These are the returns inside the sheet:

I so far tried: formating the number, formating the whole thing as number, as date, then I tried changing the sheet to clear all formating to the cell (if that would change it), tried all sorts of combinations of brackets - nothing. Help needed since I lost 2 hours of my life - for nothing.

Thank you!

p.s. I believed this to be such a simple a/b problem that I am also mind-blown by what I am doing wrong/not seeing.

[samliew]

Welcome to the Make community!

You’re close, but first you have to PARSE date before you can FORMAT date.

This is because the JSON contains a string containing a formatted/readable date, but not an actual date object.

According to the Tokens you can use to parse a date variable, you can use YYYY-MM-DDTHH:mm:ss.SSSZ.

e.g.: {{ parseDate(1.date; "YYYY-MM-DDTHH:mm:ss.SSSZ") }}

Output

For more information, see Date Formats in the Help Center or the links below.

Here are some useful links and guides you can use to learn more on how to use the Make platform, apps, and app modules. I found these useful when I was learning Make, and hope they might benefit you too —

General

• Help Center | Tutorials
• Make Academy – learn Make and get your certificate
• Discord Server – join us in live chat for fans of Make!

Help Center Basics

• Mapping – What is mapping? What can I map?
• Mapping with arrays – How to map items in an array
• Aggregate an array for mapping complex fields
• Tokens for parseDate | Tokens for formatDate
• HTTP modules – Make a request, Get (download) a file
• Webhooks – Error Handling, Responding to webhooks

Articles & Videos

• Router Magic Formula - YouTube
• Error Handlers in Make - YouTube playlist
• Getting started with OpenAI
• How to use Regex in Make

samliew – request private consultation

Join the unofficial Make Discord server to chat with other makers!

[samliew]

Module Export

You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.

1. Copy the JSON code below by clicking the copy button when you mouseover the top-right of the code block
   

2. Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the canvas.

3. Click on each imported module and save it for validation. You may be prompted to remap some variables and connections.

Click to Expand Module Export Code

JSON - Copy and Paste this directly in the scenario editor

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 97,
                    "module": "json:ParseJSON",
                    "version": 1,
                    "parameters": {
                        "type": ""
                    },
                    "mapper": {
                        "json": "{\n\"started_at\": \"2024-06-04T20:10:59.782Z\",\n\"updated_at\": \"2024-07-05T19:49:01.599Z\"\n}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 1157,
                            "y": -547
                        },
                        "restore": {
                            "parameters": {
                                "type": {
                                    "label": "Choose a data structure"
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "type",
                                "type": "udt",
                                "label": "Data structure"
                            }
                        ],
                        "expect": [
                            {
                                "name": "json",
                                "type": "text",
                                "label": "JSON string",
                                "required": true
                            }
                        ]
                    }
                },
                {
                    "id": 98,
                    "module": "util:SetVariable2",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "name": "hoursDiff",
                        "scope": "roundtrip",
                        "value": "{{round((formatDate(parseDate(97.updated_at; \"YYYY-MM-DDTHH:mm:ss.SSSZ\"); \"X\") - formatDate(parseDate(97.started_at; \"YYYY-MM-DDTHH:mm:ss.SSSZ\"); \"X\")) / 86400)}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 1413,
                            "y": -540
                        },
                        "restore": {
                            "expect": {
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "name",
                                "type": "text",
                                "label": "Variable name",
                                "required": true
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            },
                            {
                                "name": "value",
                                "type": "any",
                                "label": "Variable value"
                            }
                        ],
                        "interface": [
                            {
                                "name": "hoursDiff",
                                "label": "hoursDiff",
                                "type": "any"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

[Si]

Thank you for a very fast answer! It however does not solve the issue.

this is the input bundle for google sheets, where you can see that both solutions work the same - the end result seems to be the same.

 {
        "mode": "fromAll",
        "values": {
            "0": "515686822",
            "6": "0,9362",
            "7": "0,9468",
            "8": "0,0106",
            "9": "200673042",
            "10": "TEST",
            "11": "TEST",
            "16": "31315",
            "17": "30.98474537037037",
            "18": "30.98474537037037"
        },
        
        "insertDataOption": "INSERT_ROWS",
        "valueInputOption": "USER_ENTERED",
        "insertUnformatted": false
    }

However in google sheets, there was still the same number as from my original post.

I FINALLY solved it thanks to your ideas! It was the underlying format (somewhere) that prevented the number which obviously remained a DATE object - and was therefore treated as such when adding to google sheets.

The key was using formatNumber as a wrapper for the calculation.

Both of these functions work (for anyone having this issue at some point in the future):

{{formatNumber(((formatDate(1.payload.updated_at; "X") - formatDate(1.payload.started_at; "X")) / 86400); 5; ",")}}

{{formatNumber((formatDate(parseDate(1.payload.updated_at; "YYYY-MM-DDTHH:mm:ss.SSSZ"); "X") - formatDate(parseDate(1.payload.started_at; "YYYY-MM-DDTHH:mm:ss.SSSZ"); "X")) / 86400; 5; ",")}}

input bundle, where you can see a very subtle difference between the unformatted (18.) and formatted 17. and 19.

           "17": "30,98475",
            "18": "30.98474537037037",
            "19": "30,98475"

The result now looks like this (formatted as duration - which requires a number as hours with decimals and next to it as plain number of seconds WITHOUT /86400).

[vendy]

Hey @Si

Just wanted to step in and thank you for sharing your solution with us. Great to see you collaborating with other Makers and making the most out of our community!

Keep up the good work