SFG-20
Codes turning into dates
Excel has an annoying habit of changing SFG-20 codes into dates, for example 03-03 will be turned into 03-Mar. This guide explains how to convert them back using a nice little hack.
The broken data
This is an excerpt from a previous PPM import, you can clearly see the dates instead of the codes. 03-Jan should clearly be 03-01.
Step 1 – Reformat the cells
Select the whole of column P and at the top where it says general, select the drop down and choose more number formats.
Select custom from the category and in type enter dd-mm. What this is doing is reformatting the date from 03-jan to 03-01. It is still a date but at least now it’ll look correct:
Note that the codes are still aligned to the right demonstrating that they’re still dates.
Step 2 – Copy the cells and place them in notepad
Select the whole of column P and either right click copy or press ctrl+c
Then open notepad
And paste the whole thing in notepad by pressing ctrl+v or right click paste
Return to excel
Step 3 – Reformat again
Select the whole of column P and again go to format and this time change it to text:
Step 4 – Clear contents
Right click on the column letter (P) and click Clear contents to leave the whole column empty.
Step 5 – Return to notepad and copy
Return to notepad and press ctrl+a to copy the whole thing, we want everything so you don’t need to worry about where you click or where you copy to and from. We want all the gaps and everything.
Now press ctrl+c or right click copy
And return to excel and our empty line
Step 6 – Paste contents
In excel your column will still be highlighted, if not, highlight the whole column by clicking the title (P), then right click somewhere in column P and paste as below to enter the data.
It might say this, if so just click OK:
Your codes are no longer dates!
Vantify CAFM Support
For further assistance on anything covered in this guide or others, please contact the Vantify admin team on 01480 499740 or helpdesk.cafm@vantify.com.
This guide was created by Vantify's Product Team and reflects the latest updates as of the specified date. Please note that the information provided may evolve with future system updates or changes. Additionally, some details may vary based on your specific user permissions and account settings within Vantify.