Hello
I'm working on some data and have hit a problem I can't work out
I am beginning with text data in this form - 3 examples are listed below (Note the US date format):
10/18/2018 12:00:00 AM
10/9/2018 12:00:00 AM
9/30/2018 12:00:00 AM
STEP 1
I am using this formula to convert to a date in UK format :
=DATE(VALUE(MID(O822,7,4)), VALUE(LEFT(O822,2)), VALUE(MID(O822,4,2)))
STEP 2
I am then using this formula to get the Week Number from the calculated data in Step 1:
=WEEKNUM(G822,1)
I can't work out how to get around the problem that Step 1 doesn't work on examples 2 and 3 because the day and month only have 1 digit.
I would be grateful for any ideas how to solve this and get to the week number using formulae
Thank you
Bookmarks