convert text dates to values

[thedrinkerparadox]

Hey,
I have alot of excel export data (in red in example).
There are names and name of the months (in Norwegian - should be close enough to English).
I need to convert the red data into a table like the green box.

[JohnTopley]

In E2

=DATE(2016,MONTH(INDEX($B$2:$B$80,MATCH($D12,$A$2:$A$80,0))&0),1)

in F2

=DATE(2016,MONTH(INDEX($B$2:$B$80,MATCH($D12,$A$2:$A$80,0)+COUNTIF($A$2:$A$80,D12)-1)&0),1)

How do we know the year or finish day: you have 01 and 31 but no explanation as to why.

[Richard Buttrey]

Hi,

Why is the end date for Carl 1/12/2016, but for Lisa it's 31/12/2016. They both have Desember as the last month.

And why 2016? Is that the default year?

[sandy666]

you can do that with PowerQuery, see attached file
(you need to use Norwegian instead of English months - this is the pattern only, my result is not correct)

[CK76]

Is your Excel version English or Norwegian?

If Norwegian you can just use helper column.
=DATE(2016,MONTH(DATEVALUE(B2&" 1")),1)

Which will convert month name to date value for 1st day of the month.

If your Excel version is English... try creating Lookup table to convert month name.

See attached.

[Glenn Kennedy]

Or my suggestion...

[sandy666]

Here is localized version without changing norwegian to english & vice versa
Norwegian (Nynorsk) is used not Norwegian (Bokmal)

(PowerQuery)

[thedrinkerparadox]

Thanks everybody!
I think JohnTopleys code solves the problem.

I did wrong with the 1st and 31st. dates... there was no way to know.

[sandy666]

you are welcome, thanks for the feedback
(btw. version from post #7 is correct for you if you planning to use PowerQuery)