Date format on downloaded XLSX

Muzekker_Vatan · 09-04-2022 09:12 PM

Hello all,

Have a small form to scan a barcode with the scanned date column. The date on google sheet formatted as 5-Sep-2022.

After scan all items i am downloading the file as XLSX and date on same column shows me the date as 09/05/2022.

Any way to have the date format in downloaded file as 5-Sep-2022?

Thanks for your help in advance.

dbaum

In the template for your Create a file task, you should be able to use the TEXT function to format the date values.

Muzekker_Vatan

Thanks but the date format on GS is what i expected after scan. Problem is when i download GS as attachment.

Template is exact GS as well.

dbaum

I think I'm not sure what you're doing. I wonder whether it's one of the following or something else altogether:

Within AppSheet, Use templates in an automation to Create and save a file from an automation. I assumed this is what you're doing when I replied previously.
Outside of AppSheet, download a Google Sheets spreadsheet in Microsoft Excel format. In this case, you might need to explicitly apply your desired date formatting in Sheets or Excel.

Muzekker_Vatan

Dear Friend,

You are right. It is automation sending me report (XLSX file) every morning as attached Excel sheet.

Any possible way to have date formatted in that automated file?

Thanks for your assist again.

dbaum

What file type are you using for your template? Did you apply the desired date formatting in the template file?

Kabuliño

I'm not sure what you mean but if it's a template to create a PDF try with <<CONCATENATE(DAY([Date]) ,"-",INDEX({Jan., Feb., Mar., Apr., May, June, July, Aug., Sept., Oct., Nov., Dec.},MONTH([Date])),"-",YEAR([Date]))>> , you could also try with a virtual column with the same formula: CONCATENATE(DAY([Date]) ,"-",INDEX({Jan., Feb., Mar., Apr., May, June, July, Aug., Sept., Oct., Nov., Dec.},MONTH([Date])),"-",YEAR([Date]))

Muzekker_Vatan

Dear Kabuliño,

Thank you traying me to help.

The template is coming direct from the GS. The GS is template itself actually.

I cannot use VC. Because VC is creating as last column of the table. I cannot change column ranking. Downloaded file is uploading to another software. So i am trying to have less touch to excel file receiving from appsheet automated.

Hope i was able to explain 😞

dbaum

@Muzekker_Vatan wrote:
It is automation

Based on some of your comments in this conversation, it's still unclear whether you're using an AppSheet automation. For example, "The template is coming direct from the GS. The GS is template itself actually." sounds as if maybe instead you're receiving a copy of your app's data source via some means separate from AppSheet.

If you are referencing an AppSheet automation within your app that creates an Excel file, I suggest you post screenshots of the automation's steps and of the template used for the Create a file task.

Steve

@Muzekker_Vatan wrote:
Any way to have the date format in downloaded file as 5-Sep-2022?

Have you tried setting the column format of the template file? I would expect/hope the cell formats of the generated Excel spreadsheet would match those of the template. It's possible it won't though. Worth a try.

Muzekker_Vatan

Dear Steve

I did not create a template actually. Made automation daily report and took direct google sheet as attachment for this automation.

Automation is tacking google sheet as attachment using XLSX format and sending to me every day.

Date format on my app form and google sheet column are ok as 05-Sep-2022. The problem is that automation is sending me XLSX file and date format is as 09/05/2022.

Sorry for your time. Hope it i explained clearly. Thanks.

dbaum

@dbaum wrote:
you might need to explicitly apply your desired date formatting in Sheets

What you're seeing may just be the default date format, which then translates to whatever is Excel's default. Try overriding the default with an explicit setting and maybe that will carry over to Excel.

Muzekker_Vatan

Dear dbaum,

I changed excel default date format from 08/09/2022 to 08-Sep-2022 by using Microsoft settings as below.

The format is changed on google sheet as below too.

From of my app is adding to google sheet as 08-Sep-2022.

The problem is as i mentioned previously; when automation takes the template from google sheet date is coming as 09/08/2022

Thanks.

dbaum

@Muzekker_Vatan wrote:
The format is changed on google sheet as below too.

If by "google sheet" you're referencing your data source file and not the following template file, which is what AppSheet uses to create the attachment, then try applying the date formatting in the template file.

(To be clear: I don't have any particular insight into how best to address the issue you're facing. I'm just suggesting troubleshooting steps that may be worth trying and that aren't clear to me whether you've already attempted them.)

Muzekker_Vatan

Dear dbaum,

Here is my template i created as attachment. And the report i am getting from automation.

Company	Container Type	Container No.	Mail Tag No.	POL	POU	Dest	Flight Number	Flight Date	CMP CODE
DHL	BULK	LHRU2	VNHANANOOSLAAUR20015010010180	LHR	IST	IST	1234	09-10-2022	CMP03
DHL	BULK	LHRU2	VNHANANOOSLAAUR20017001010146	LHR	IST	IST	1234	09-10-2022	CMP03
MAIL AMERICAS	BULK	LHRU2	VNHANANOOSLAAUR20017009010193	LHR	IST	IST	1234	09-10-2022	CMP20

Company	Container Type	Container No.	Mail Tag No.	POL	POU	Dest	Flight Number	Flight Date	CMP CODE
<<Start: Filter(POST MAIL, true)>><<[Company]>>	<<[Container Type]>>	<<[Container No.]>>	<<[Mail Tag No.]>>	<<[POL]>>	<<[POU]>>	<<[Dest]>>	<<[Flight Number]>>	<<CONCATENATE(DAY([Flight Date]) ,"-",INDEX({Jan., Feb., Mar., Apr., May, June, July, Aug., Sept., Oct., Nov., Dec.},MONTH([Flight Date])),"-",YEAR([Flight Date]))>>	<<[CMP CODE]>><<End>>

Kabuliño

If you are already using the formula that I proposed... , is there any problem with changing the date format to text?

dbaum

@dbaum wrote:
In the template for your Create a file task, you should be able to use the TEXT function to format the date values.