Struggling to get a date? It's all in the set up....

So, hands up - who has been set up on a blind date by mutual "friends"? And how did that go for you? Disastrous? Embarrassing? Live happily ever after?

Going on the film versions, blind dates never work out that well. Because everyone has a different idea of what an 'ideal date' is. And two people, although they may seem to want the same thing, will have different preset ideas of what that 'thing' is. I think it is safe to say that in general, blind dates should be approached with as much care as you would use when trying to tackle a rabid tiger. Who has a sore tooth.

The same goes for Excel (not the tackling the tiger bit). Two people will have a different approach to a problem in Excel, because they have different preset ideas, or ways of working. As I keep on repeating, there is always more than one way to find a solution in Excel. A perfect example landed in my email box this week. In my newsletter last week, I pointed out that I was kinda running out of ideas for this blog.

(Side note - please note this point and feel free to comment below with topics you would like covered or problems you would like solved....)

Anyway, I got a lovely email from Mr B, who provided me with a variety of solutions on how to calculate the difference, in hours, between two dates and times. Now, working with dates and times in Excel is tricky. I have already done a basic blog on this (click here for a reminder), but Mr B reminded me of a brilliantly handy formula - the DATEDIF function. What this formula does is calculate the difference between two dates, but for specific criteria. The best way to explain this is to show you. Take note of the letters in inverted commas in the formulae, that is what defines the result you get:

The last 3 need a bit of explanation. Days only simply looks at the 'day' part of the date and subtracts one from the other (so 16 - 2). It ignores the months and years. The same goes for month only (so 8-5 in our example). Dates, ignoring years, assumes both dates fall into the same year and just calculates the difference in days.

Note when using DATEDIF that your Start Date comes first in the formula, and must always be earlier than the End Date.

So, after that whirlwind tour of the DATEDIF function, lets look at what Mr B sent me. Solution 1:

Here Mr B uses the DATEDIF, HOUR and MINUTE functions to break down the difference into days, then minutes, and then takes the answer back up to hours. Go through it at leisure...

Solution 2:

Again, go through this at your leisure, but he has shortened the equation by using the IFERROR and  MOD functions, which in this case basically say, if the answer is negative, then subtract the answer from 1 to get a positive.

These are both good solutions, and my thanks again to Mr B for sending them on. Clearly though, I have a female brain, so immediately thought of a different solution. I would use formatting to get this right. Under custom formats for numbers, you can select the format dd/mm/yyyy  hh:mm. So go to Home, Format, Number, Custom and scroll down till you find this beauty.

Then, to get the date and time into one cell, you would add the 2 together. So in the example above, to get the first start date and start time combined into one cell, it is as simple as =B4+A4. Doing this, you can get the table below:

To get the difference in hours, simply use =C3-C2, and keep the result cells formatted as numbers.

So, like I said, different strokes for different folks, but it all comes to the same answer.

Job done, another dating issue solved....

If you're new to this blog, and would like a reminder every time a new blog is posted, why not sign up here?

Happy spreadsheeting, and remember to send on any queries!