LinkBack URL
About LinkBacks
Hi
I have the following code that im trying to use to extract the day and month portions of a date.
I cant understand why im getting an "Expected array" error. any ideas?
thanks
Please Login or Register to view this content.
Hi
I have the following code that im trying to use to extract the day and month portions of a date.
I cant understand why im getting an "Expected array" error. any ideas?
thanks
Please Login or Register to view this content.
Your if statement is trying to use MonthBit as a function;
What are you trying to compare here, and what do you want the outcome to be? It's not clear, and I'm pretty sure that code doesn't do what you intend, even with the array error fixed. Can you clarify what you are trying to achieve?Please Login or Register to view this content.
There seems to be an end if missing, too...
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
you're right olllyxls.. i changed that bit of code...removed (cell.value) and that error disappeared.
What im trying to do is swap the date and month portions of the date. Im using the format "dd/mm/yyyy"
So, given 12/10/2013, i need to change that to 10/12/2013. Do you know a more efficient way?
cheers
Originally Posted by wazimu13
Possibly. What is your desired criteria for swapping them, as the code posted above would compare with today's date...?
Might be easier to paint a fuller picture of the problem you are trying to solve. You obviously want to work through a range of data, identify the day and month, and possibly switch these values. What format are your dates stored in (date / text) ? Are you trying to correct locale issues, where you've received dates in m/d/y format, and you want them as d/m/y?
Maybe attach a workbook too. It should all be straightforward, but tell the whole story, and make the requirement clear.
Basically, i downloaded a data extract from SAP, and the date column has dates that look like this " 03.12.2013 ", which is dd.mm.yyyy, so no locale issues.
So my first step was to replace the fullstops with "/". i then formatted the range as a dd/mm/yyyy date.
The problem was as soon as i stepped to the next line of code, the day and month bits swapped!
So the above code was my attempt at manually reversing this strange behaviour (have you come across this before? im on excel 2010)
The code to select the date range, format the date and replace the fullstops is:
After doing this im still getting the following output. The top two should be 5th and 6th Dec 2013. The next dates are correct. This just happens on some datesPlease Login or Register to view this content.
and thats why i need to then go through each date to make sure its correct
12/05/2013
12/06/2013
23/11/2013
23/11/2013
23/11/2013
23/11/2013
hope this makes it a bit clearer. i will be doing this check daily so it will check against the current year. so how could i swap the date and months please.
cheers
Okay - stop trying to find a fix for a bodge, let's get your data converted correctly in the first place.
Try using the following code to convert your sap imported text to dates:
Please Login or Register to view this content.
Thanks Ollyxls!i think that did it!
Glad it's sorted, thanks for the feedbackThanks Ollyxls!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks