Forum Discussion

StrataGeek's avatar
StrataGeek
Experienced User
7 days ago

Bug: Excel date fields are not "Date" format

Hi there,

 

When exporting, say, the Transaction List as Excel, the date fields are not interpreted by Excel as "Date" format, but as "General", which is effectively "Text" format as far as using the data in the field.

 

For example, I can't enter a date somewhere and then test via a formula whether the MYOB-generated dates are before or after that date.

 

I have a vague recollection from when I used MYOB Premier and MYOB AccountEdge many years ago that they did a similar thing.

 

Can the devs of any affected MYOB please set the date fields as "Date" format?

 

I know they have control over this because dollar figure fields have a "Custom" format of "\$#,##0.00", so they know how do change field formats.

 

Even a date-related "Custom" format would be acceptable, as long as Excel treats them as numerical dates (i.e. day number 1 = 1/1/1900).

 

Thanks,

 

Sean

2 Replies

  • Jared-Solo's avatar
    Jared-Solo
    MYOB Staff
    5 days ago

    Hey Sean StrataGeek​ - we're always learning something new from you, so thanks for flagging this.

    I played around in Excel on my Mac - both the app and the web version - and I was able to bulk convert the generic text format into true Excel dates.

     It works a bit differently across the Excel Mac app and the browser, so your mileage may vary, but here's what I did:

    StepMac appBrowser 
    1highlight the date column or cellshighlight the date column or cells
    2go to Data > Text to Columnsgo to Data > Split Text to Columns
    3click Next, then Next againclick Apply
    4choose Date and select the matching order (for example DMY) 
    5click Finish 


    After that, the cells should become real Excel dates, and then you can format them however you like and run your before/after date checker formula as normally.

     

    I'll see if anything can be done on our side to render those fields as proper date values from the start, but in the meantime this could be a good workaround. 


    Let me know how you go! 

  • StrataGeek's avatar
    StrataGeek
    Experienced User
    5 days ago

    Thanks Jared,

     

    Yes, I did know how to do that, it's just that that's a pretty blunt force sort of way to have it if any dates are in columns with other sorts of data formats.

     

    Sean

Looking for something else?

Search the Community Forum for answers or find your topic and get the conversation started!

Community home

Related content