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
@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
@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
@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.
@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.
Hopefully this makes sense. First 2 photos the report template. Next 2 are actual report output.
@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
@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
@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
User | Count |
---|---|
41 | |
31 | |
31 | |
16 | |
14 |