Log In
Register
Home
May 8, 2018
Offline
Hi Mynda
Hope you can help.
See attached file “MyTestData”. This Data is exported from a SharePoint list and supposed to retain the link to SharePoint to enable updates whenever the Excel file is refreshed or manually set to refresh upon opening. However, I tweaked the real Data and removed the link for the sake of my problem. The problem I have is with the dates, especially the dates in Column H which is the source for my formula.
My problem as follows:
- All date fields in the SharePoint list is set to Text. I cannot change it as I am not the site owner or a contributor with editing rights. Capturers just captures any crap in there and don’t even capture the date as Text in the South African format.
- In the exported list (Table) in Column I, when I try to write the following formula it does not work until I either change the date settings to a date and retype the date in yyyy/mm/dd format or refer to the actual cell e.g. H2 then again in some instances the formula does not even want to work or reflect the incorrect result. The formula is based on the data in Column H:
=IF([F1-Expiry Date]<TODAY()+360,"0 - 12 months",IF([F1-Expiry Date]<TODAY()+720,"12 - 24 months",IF([F1-Expiry Date]<TODAY()+1080,"24 - 36 months","36+ months")))
How can I make this formula work for me and ensure that every time I refresh the data, all the dates (especially those under Column H) are formatted correctly. I need the South African version i.e. yyyy/mm/dd. The result will feature in my Dashboard. Is VBA required? Please help.
October 5, 2010
Offline
Hi Amien,
No file(s) attached. You need to click on the Start Upload button.
Without seeing your data I can't be certain but you my benefit form using Power Query to get the data from Sharepoint. It would help you out with the dates problem.
Once the data is in Excel, you can set the display format for dates by selecting the date cell(s), pressing CTRL+1 and then clicking n the Number tab.
Click Custom and then on the right of he pop-up window under Type enter yyyy/mm/dd
Regards
Phil
May 8, 2018
Offline
Dear Philip
Oh no man! I probably did not follow through with the upload. Anyway, thank you very much for the response. I will try Power Query and load the Data to a connection only as I will still have the original table on a separate sheet that needs to be refreshed.
I tried uploading the file again. If you see it on your side please have a look at it if you don't mind. If its not there, it could be that I am having connectivity problems.
October 5, 2010
Offline
Hi Amien,
You need to use Power Query to get the data into Excel from Sharepoint. That will fix your problem with dates in text format.
To illustrate this I've created a query from the table in your workbook. All the dates in Cols E to H are text. PQ immediately recognizes them as dates and converts them.
Saving the query result back to Excel, these dates are proper Excel date serial numbers. All you then need to do is select the dates and use CTRL+1 (as I described in my previous reply) and set the display format to yyyy/mm/dd.
Your formulae should now work. I've left that bit for you to do 🙂
You may find it useful to check out a few articles. In the Resources menu at the top of the site, read through the Time & Dates in Excel, and the Excel Custom Number Format Guide.
Regards
Phil
1 Guest(s)
