Excel date format (if text or 5 digit)

[AlphaTee]

Hello all, I have been struggling with this issue for a couple of days and perhaps a fresh pair of eyes might help resolve this.

I have:

1. checked Excel is using 1900 date format (using a Mac which sometimes may use 1904 from what I have read)

2. Read other posts that include e.g.: {{ parseDate(46792 * 86400; “X”) }} as a way to convert the 5 digits that Make reads when there is a date – but nothing seems to be working including checking both my regions in Make and standardising the date formats in Excel (ie using US rather than Canada/UK etc)

See the attached, if the Excel sheet has 45446 as the date (ie 3rd June 2024, entered as 3-Jun-24 and formatted as a date in the Excel sheet) as pulled by Make I just cannot seem to get the right date! – I even created a new tab and tried various methods such as:

1. Column with date formatted as text
2. Column with date properly formatted as a date

However, I just cannot seem to get things to play nice with each other! – Any advice on what I may be doing wrong?

Please ignore the “4” day adjustment in addDayes – I was using “-2” and that was just a test to see if I can identify the issue.

[L_Duperval]

HI @AlphaTee,

I’m not sure what you’re trying to do. Can you explain:

• What input you’re getting
• What output you’re trying to get (without using Make functions)

From what I gather:

• Input: 45446
• Expected output: 06/03/2024
• Received output: ??? (there are 5 different ones in your example)

Is that correct? It seems to me like examples 4-7 are based on the value 0, meaning that whatever input you are getting resolves to epoch, so 1969. Maybe a text value which becomes 0?

I’m kinda wondering if the problem isn’t the input data, not the output.

L

[AlphaTee]

Hi, thanks for your reply.

Apologies for the confusion. Let me clarify what’s happening.

I’m importing dates and other data from Excel into Make. The dates in Excel are formatted as ‘DD-MMM-YY’, but when they are brought into Make, they appear in a five-digit format (e.g., 45446). I then need to convert this five-digit format back to the original ‘DD-MMM-YY’ format so it can be correctly used in subsequent steps for data entry.

The different outputs in the screenshot are just my attempts to figure out which formula would correctly translate the five-digit format back to the intended date format.

Hope that clears things a bit, please let me know if I should further elaborate on this.

[L_Duperval]

HI,

OK, I think the probelem you have is tthe “*”. Notice how it’s black instead of green. Make sure you use the “*” in the math formula section and it should work.

I’m also unsure about the 86400, but anyway, here is what I came up with:

Overview:

Configuration of trigger:

Configuration of set variables:

Execution:

Hope that helps. Ask away if you have more questions!

L

P.S. Oh, the reason it might not work is that I used Google Sheets as the input tool instead of Excel. So the starting number might be different, I’m not sure.

[AlphaTee]

Thanks again for the detailed reply. I just got back home and immediately tried your suggestions.

Unfortunately, I seem to get a date of 4th June 1994 (ah yes, a trip down memory lane!) so that is far from the expected 3rd June 2024.(ie showing as 45446 on make)

However, good catch on the “*”-- that is exactly why I needed an extra pair of eyes to see what I am doing wrong!

Now, if you have any advice on:

1. Why this issue is happening (I wil try Google Sheets as well and shift to it if need be, but a solution might just help someone else who is facing the same and wants to use Excel)

2. You very kindly provided the “parsedText” and “parsedNum” – The latter worked once and then seems to error after that (DataError) and the former simply does not work, but I will try again.

[L_Duperval]

Hmm… There is a 70 year difference between the value returned by the parseDate function and the expected value.

In Google Sheets 0 is 30/12/1899
In Excel, it’s 1/0/1900
In Make it’s December 31, 1969 7:00 PM

So if you add estimate the number of days between 0 in Make and The other two, it’s 25,567.

So remove that value * 86400 and you’ll get the correct date:

You may need the X when you parse the date, but I tried with and without and it seems to work.

L

[L_Duperval]

Here’s the blueprint

blueprint-date-prsing-epoch.json (6.3 KB)

L

[Msquare_Automation]

Hi @AlphaTee
I have created a sample sheet and got the following output dates.

In this instance, the output represented the number of days since “1899-12-30”—note that this may differ for you.
To address this, I used the function {{addDays(“1899-12-30”; 8.values.2)}} .

If you need further assistance, feel free to DM me.

Output:

Best regards,

Msquare Automation
Gold Partner of Make
@Msquare_Automation

[L_Duperval]

Cool, that’s a very elegant solution.

L

[AlphaTee]

@L_Duperval Thank you so much for being the first to brainstorm this problem, your initiative helped to keep the momentum going. I really appreciate that, and your advice actually helped me to learn even more about Make and how to think through a problem.

@Msquare_Automation Yes! This solves it and it is now working. Your solution is indeed elegant and there is no way I could have figured it out. Thanks for adding value to the community!