Someone I was training recently had been given a spreadsheet with dates from the beginning of the month – some as calendar dates – ending is st, nd, rd, th and some with no suffix which were business days from the beginning of the month. He wanted to be able to convert all of these into actual dates.
In cell C1, I entered the first date of the month, in this case, 1/11/2017.
May be if I sit down and look at it for longer, I could do this all in one formula, but I did it in three stages.
In cell C4, I entered the formula
This looks at what was in cell B4 and if it didn’t have a suffix, used the WORKDAY function to work out the date in cell B4. I then copied down the formula to the rest of the dates. This then gave me a date for all those with business days and an error message for those with calendar days.
In column D4, I entered the formula
This looks at the length of the value in cell B4. If it is a calendar date, it then gets rid of the suffix by using the LEFT function, combined with the LEN function (which looks at the length of the string) to retrieve just the day and concatenates it with the month and year of the first of the month in cell C1.
I then need to combine these into one column. In cell D4, I used the following formula.
This returns the value of C4 if there is a date there, otherwise it returns the value of D4. Also ensure that the column is formatted as Date.
Columns C and D could now be hidden.
Find out more about our Microsoft Excel training London, UK.
Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.