Techie Post - Nested If in Excel for converting month to a number

This post is more of a reminder for me, but may help someone else in a similar situation.  Every year in August when I need to create the new user accounts for the students starting in September at school, I need the student date of births to be in the format DDMMYY so that my custom powershell script can create the accounts in Active Directory

Sounds simple, but alas our School Information Management System exports the data in a weird format, so I am always having to modify and fix the exported data in excel before I can import the completed csv file.

Most can be accomplished using the excellent left, mid and right formulas, but the date (more specifically, the month) always causes me issues.

So, where I a have a cell where the month is "Jan", or "Feb", I need it to be "01" for Jan, "02" for Feb and so on.

So.... assuming cell F2 is the cell with the month in it, the following nested If formula will do the job

=IF(F2="Jan","01",IF(F2="Feb","02",IF(F2="Mar","03",IF(F2="Apr","04",IF(F2="May","05",IF(F2="Jun","06",IF(F2="Jul","07",IF(F2="Aug","08",IF(F2="Sep","09",IF(F2="Oct","10",IF(F2="Nov","11",IF(F2="Dec","12",))))))))))))

Once I have the month as a number, I can easily combine the 3 numbers making up the day, month and year (ddmmyy) in to a cell for my script to accept.

I'm sure there's a better way of doing it, but hey ho...  This does the job for now.

Winter, hurry up!

I've got to say, I've been rather enjoying this summer weather over the past few days.  "What?  Stuart likes the summer?" I hear you say.  No, not the usual sunny, hot temperatures, but the rather impressive thunderstorms that we have been experiencing lately.

One thing I can not stand is when the press/news go on about the 'awful, wet weather', and that when the sun and hot temperatures come back, it's all "Glorious, lovely weather returns".  Wrong - Not everyone likes the hot, sunny weather.  Stop assuming that everyone likes blistering heat, and blinding sun.  I hate it in fact.  I suffer from severe hay fever, the heat and sun gives me terrible headaches, I sweat so much that I feel like I need to shower every ten minutes.  I cant sleep at night when it's still so humid, and I feel terribly uncomfortable all day long.  I hate it.  I can not understand the appeal of summer.  The sight of half naked men walking down the street, showing off their disgusting bodies (seriously girls, do you find that attractive?).  Being stuck in a queue right behind someone who has BO - I mean seriously, instead of buying several cans of lager, treat yourself to some deodrant, or soap!  Also, I can think of a thousand better places to be than on a overly crowded beach, surrounded by hot, sweaty, smelly people.  And is it just me, or do others also find beaches such dirty places, with litter and rubbish left by these so called sun worshippers?

AND don't get me started on men in flip flops and sandals.

So, it has been rather refreshing that this year we've had some spectacular thunderstorms, and quite a bit of rain to help cool the air.  OK, so the thunderstorm that we had yesterday caused a power cut at the school resulting in me having to pop in to work for an hour (on a Saturday), but hey - that's a small price to pay for such lovely weather.

So, for all you people out there who think hot sunny weather is the best - spare a thought for people like me (and I know there are many others like me) who suffer during this dreadful time of year.  The best weather is when it's cool and raining.  Bit of wind and snow is nice too - but definitely not the sun or heat.

Roll on the Winter, and bring a few more thunderstorms our way please!

IN OTHER NEWS....

After a sad final last day of term on Friday where I had to say goodbye to some wonderful work colleagues/friends who have decided to move on to new adventures elsewhere, the school summer holiday is finally upon me (where has the past year gone?), which means six weeks of stress and worry while I get both Evesham High School's and Simon de Montfort Middle School's networks merged in to one.  Not great when I'm having my office completely redecorated at the same time.

My office, as it currently looks - Who needs a desk?

 

One of our ICT Classrooms (DU1), currently being used as a storage room while my office is painted, and a new carpet placed down.

 

It's going to be difficult getting things done whilst the decorators are in, so I am really hoping that they'll be able to get it finished early this week so that I can start with the actual merge the following week.  That and setting up a new suite of computers AND a new Exchange Server.  This summer holiday is going to go down on record as being the most stressful ever.

If I make it to September without a coronary, I'll be amazed!