Hi @maxpressi 

In Excel you’ll find that column is set to a “Date” formatting. This means it will take whatever you input and reformat it per its own rules.

You should be able to change it to a “Plain Text” mode, then it won’t change the input format.

Hi @AndrewJDavison_Luhhu !

Thanks a lot for your reply.

I tried to use Plain Text but the output from Zapier is a number.
Please see attached (Col F).

And my excel date format is the good one.

EDIT: On this screenshot, data in is 02/04/2020 (which is 2nd April 2020 in France) and date output is 43865 which represents 4th Feb 2020 on Excel format. So the problem comes before filling the sheet, I think.

Any suggestion ?

Thanks a lot for your time!

Maxime

Hi @maxpressi 

Can I see what you’re mapping for Column F?

Sure! Please see screenshot.

03/04/2020 is from an email and means 3rd April 2020 (which is already the good format).

Is it enough for you ?

@maxpressi 

In your past post, you showed the “Data Out” tab for a recent test. Can you show the “Data In” tab.

I think what you’ll find on that tab is the date is in the correct format. If so, that confirms the change is happening inside Excel.

Correct, Data In is in correct format.

But I think Data out is wrong. Because I made a test on Excel, and 02/04/2020 using French format date (2nd April 2020) should be 43923 number and not 43865 as it is showed on Data Output.

Do you think we can override or workaround this ?

Thanks,

Maxime

Hi @maxpressi

Ok, thanks!

So, if things are looking good in “Data In” but not “Data Out” what we can tell is Excel is doing something in the middle there to mis-format the data.

I know I say to switch that column in Excel to “Plain Text”, but could you try switching it to “Date / Time” format instead, and making the format in Excel matches up with what you want.

If that doesn’t correct things, we’ll try some others.

Hi @AndrewJDavison_Luhhu 

I tried with good cell format on Excel but it still the same.

Another thing, when date is > 12th day of the month, then the cell is well filled.

Example :

• 02/04/2020 (2nd April 2020) will be 04/02/2020 on Excel (wrong format
• 25/04/2020 (25th April 2020) will be 25/04/2020 on Excel (good format)

In this case, number in Data Out is good.

I have no idea how to solve this problem

Best,

Maxime

Hi @maxpressi

That’s Excel swapping the DD and MM of course. I wonder if there is a further option in Excel to set it between European/US date interpretation mode?

Hi both, @maxpressi I think I am having exactly the same problem as you and have explained my understanding of what’s going on below, I hope it makes sense..

My limited understanding of dates in excel is that all dates are produced as a number - number 1 is 1st Jan 1900 and every following number up is one day on from that date, eg number 31 would be 31st Jan 1900 and 1st Jan 2020 would be 43831.

The problem we appear to be experiencing is that zapier produces the excel number format based on mis-reading the date source and interpreting it in US format MM/DD/YYYY regardless of what format information we input into all the settings.  As soon as interpreting it in the US format is impossible (13th of the month) it will probably start working again.  Because Zapier seems to always want to read it in US format, the number produced is wrong as DD/MM/YY is transformed as if it were MM/DD/YY. 

eg. If excel interprets that number/date it means that the 1st April 20 (43922) has been transferred over as if it were 4th January 20 (43834) both of which could be written as 01/04/20...

If you add a task and use the format editor and change the date format to DD/MMMM/YYYY so the month and day can not be confused by zapier/excel then this makes the date go through to excel correctly.

The problem is

1. this shouldn’t be happening
2. if you use formatter for all your dates then you’ll burn through your monthly task allowance for what seems like a zapier-excel integration error.

Interested to hear your thoughts,

cheers

Ed

Hi @eskinner !

Thank you so much for your comment. This is exactly what is going on!

You’ve made an exact summary of  ​what is happening.

I tried your solution to make a DD/MMMM/YYYY format, but it’s still not working !

So the problem is coming from Zapier, that follows an US format date when it converts to number, before sending it to Excel.

Do you think we can find a workaround ?

Thank you so much,

Maxime

Hi @maxpressi,

If someone in Zapier support @AndrewJDavison_Luhhu can explain that the process behind the creation of the number exported to excel it would help. 

Regardless, if it’s Zapier that converts the date to a number then there’s probably a bigger Zapier issue which we can’t resolve and if it’s Excel that converts it then it will probably need Microsoft support to assist in understanding what’s going on and why they’re mis-interpreting the data from zapier.

In the short term I can’t think of any workarounds other than ensuring that the date formatter is set up correctly so I’ve attached a screenshot of the settings I used to get the date formatter to work to check you tried the same.

Because I don’t have this problem with Google Sheets or SmartSheets it does suggest to me that there is an integration/date conversion issue between Excel and Zapier but that is pure speculation on my part.

Interested to hear if you get the formatter to work.

thanks

Ed

apologies here’s the screen shot

Hi @maxpressi If the Data in for the Excel step looks right, and the Data out is wrong, then in the majority of cases that means that something is happening after Zapier sends the data (ie Zapier send the correct information and Excel does something to it). 

So that we can make sure that we know what’s happening and get to the bottom of it, I've escalated this to the Support team and someone will be touch via email to help you with it as soon as possible.

Hi @maxpressi - I’m just following up here. Looks like Laura from Zapier Support responded and we wanted to share the answer in case others are looking for the same solution.

I had a look at the Zap, and the issue here is that, although the transformation is happening correctly in the Formatter step (Step 2), the results of that transformation are not being used in the Excel step. Right now, it's using the date coming from Mail Parser. To fix this, you can map the output of Step 2 in the Date cell in Excel:



This should give you the correct date!

Would you give this a try and let me know how it goes? I'll be on the lookout for your answer!