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

(William Stewart) #1

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

(Tony Fader) #2

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

(William Stewart) #3

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

(Tony Fader) #4

@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.

(William Stewart) #5

@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

(Tony Fader) #6

@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.

(William Stewart) #7

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

(William Stewart) #8

@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.

(Tony Fader) #9

@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])?

(William Stewart) #10

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) #11

@William_Stewart

(William Stewart) #12

@William_Stewart

(William Stewart) #13

@William_Stewart

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

(Tony Fader) #14

@William_Stewart Can you share the formula that you’re using?

(William Stewart) #15

@tony =IF(MONTH(NOW())>=04,CONCATENATE(“01/04/”,YEAR(NOW())),CONCATENATE(“01/04/”,YEAR(NOW())-1))

(William Stewart) #16

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

(Tony Fader) #17

@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.

(William Stewart) #18

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

(William Stewart) #19

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

(Tony Fader) #20

@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?