Hello, I have mentioned in a previous post bu...

Hello, I have mentioned in a previous post but is there any reason why virtual column date format would be MM/DD/YYYY when Sheets is set to English (Australia), and appsheet is matched? Google Chrome set to English (New Zealand). Any pointers for other things I should check. I would like to use DD/MM/YYYY but if I canโ€™t get the virtual columns to display this way I will convert all date columns to MM/DD/YYYY Thanks

0 20 1,037
20 REPLIES 20

tony1
Participant V

@William_Stewart Can you make sure that the column type of your virtual column is Date?

@tony thanks Tony. But yes, definitely date type. Display is accurate, just not the format I want

tony1
Participant V

@William_Stewart Okay, Iโ€™ve been able to track down the issue.

The short story is: when youโ€™re building up a date using text functions like CONCATENATE, you need to use MM/DD/YYYY format. App formulas use that format internally, so you need to be consistent with that.

Now, thereโ€™s a bug, which is when displaying a date-valued virtual column in a form view, it always uses MM/DD/YYYY for the display. In other views, it shows up correctly.

So for the mean time, please use MM/DD/YYYY format when building your date value using text formula(s). Iโ€™ll look into the bug and whether this whole thing could be simplified.

@tony Thanks Tony, simplified would be great! Just to clarify, does this mean I should also change my date format in Sheets - or will it all work out? I can change the format as long as I know what to use. Thanks again

tony1
Participant V

@William_Stewart No, you only need to change the way you write your formula.

Instead of: CONCATENATE(โ€œDD/MM/โ€,YEAR(NOW()))

Use: CONCATENATE(โ€œDD/MM/โ€,YEAR(NOW()))

That will keep your date values in the right timezone.

But! There is still the bug with the form view that I mentioned. So your virtual columns will show up in DD/MM/YYYY format when in a form view, but should be good everywhere else.

@tony Think maybe you meant for one of those to be DD/MM and the other MM/DDโ€ฆBut unsure which??

@tony Also, no matter which format I use in the VC, when I call upon the date in my Google Doc report template, it displays MM/DD regardless.

tony1
Participant V

@William_Stewart Oops, sorry that was a copy/paste error.

What I mean is, when constructing your date using the MM/DD/YYYY format, like this:

CONCATENATE(โ€œMM/DD/โ€,YEAR(NOW()))

In your template, can you try using TEXT([Your virtual column])?

It appears to be showing the date correctly now, but It is not working within my expression. See the attached. The dates at the top are

Last FIscal Start

Last Fiscal End Current Fiscal Start Current Fiscal End

My expression works if I manually set Start/End dates i.e. 01/04/2017 & 31/03/2018 but if I use the VC then it captures all expenses regardless of the date the expense occurred. See attached images.

@William_Stewart

Hopefully this makes sense. First 2 photos the report template. Next 2 are actual report output.

tony1
Participant V

@William_Stewart Can you share the formula that youโ€™re using?

@tony =IF(MONTH(NOW())>=04,CONCATENATE(โ€œ01/04/โ€,YEAR(NOW())),CONCATENATE(โ€œ01/04/โ€,YEAR(NOW())-1))

If I set the format to Longdate, then this expression would display as Jan 4, not Apr 1. I wonder if I have some parenthesis

in the wrong place

tony1
Participant V

@William_Stewart One thing is it looks like youโ€™ve got an extra comma in your expression. The โ€œ,โ€ should just be โ€œ,โ€.

Can you try removing that and see if the problem still occurs?

Otherwise, Iโ€™d like to take a look at the app itself. Please give me the name of the app, the name of the table, and the name of the column with the app formula.

Hi Tony, will try that but I think in fact I have removed that on my other 3 VC and the result is the same. Otherwise I will confirm

Thanks @tony that was the only VC with double comma, have ammended . Note, to show correct date I am currently using MM/DD/YYYY in expression, but all locale etc should be set as Australia. App name, Property Management App. Table name Property, Virtual columns: Current Fiscal Start, Current Fiscal End, Last Fiscal Start, Last Fiscal End. Thank you, I appreciate your time

tony1
Participant V

@William_Stewart So youโ€™re saying that if you concatenate a date together in MM/DD/YYYY format, it will display correctly in your app correctly as DD/MM/YYYY?

@tony just re read your question . If I concatenate in mm/dd/yyyy and use short

date it displays the same as mm/dd/yyyy. I was getting confused as have been displaying in log date format to check. I want dd/mm/yyyy as I use this format throughout my app and of course this is our common format in nz

Top Labels in this Space