Excel 2007 : Time Formula

**AlanWade** · 11-17-2008, 09:33 AM

Hi,

I have two columns in my work sheet, one for journey start time and one for journey end time. At the moment, I have another column setup I1-H1 to give me the time to travel between jobs. Both columns are formatted to the 24 hour clock (eg 16:00). What I would like to do, if possible, is if I enter a journey start time which is before 16:00 then the calcuation will not start untill 16:00.
For example if I type start time 15:50 and end time 16:30 then the result would be 30 mins not 40.

I have enclosed an example of my work sheet so you see what mean.

Any help is appreciated.
Thanks in advance
Alan

**darkyam** · 11-17-2008, 06:11 PM

No attachment, but formula would be =I1-Max(2/3,H1). Note that this assumes you won't be travelling overnight.

**AlanWade** · 11-18-2008, 04:25 AM

thats fantastic! thankyou

One problem I found is if I arrive home before 16:00 it shows a negative value which in turn is deducted from the months total, can I have negative values show and caculated as 0?
Now, at the risk of being cheeky (which I can assure you thats not what I want to be ) at the other end of the scale can I adapt this to if I drive before 07:30 but arrive after 07:30, it will only calculate up till 07:30.

Is it possible to explain the formula in simple terms, I find it intresting but dont understand it.

Thanks Again
Alan

**darkyam** · 11-18-2008, 10:18 AM

Try =IF(I1="","",MAX(0,MIN(39/48,I1)-MAX(2/3,H1)))
Instead of fractions, you could use Time(19,30,0) for 7:30 and Time(16,0,0). I use fractions only because it's easy for me to think in terms of fractions and I like short formulas.
An IF formula has three parts, the logical test, the value if true, and the value if false. If I1 is blank, this formula will return a blank, otherwise it will calculate the value. Max just takes the largest value in a given list. Putting Max(0,<rest of formula>) in makes it read as 0 if the value is negative. Min is simply the opposite.

**AlanWade** · 11-18-2008, 10:37 AM

I couldnt get it work, I changed all the comma's to semi colons (I have excel in Swedish) but still it wouldnt work. Maybe, if you have time, you could look at the attachment and tell me where I am going wrong.

Thanks for all your help
Alan

EDIT
Forgot to change IF to OM

That works great.

Now with the morning journey, its reversed so that I count everthing before 07:30 but nothing after.

**AlanWade** · 11-18-2008, 11:28 AM

I just cant get the formula in reverse

This time I would like where a1 is the start time (Before 07:30) and B1 is the end time, as before but only calculated up till 07:30
eg a1 = 07:00 and b1 = 07:35, it will give an answer of 30 mins But I just cant get it.
Please can you help again.
Alan

EDIT

GOT IT!

Thanks darkyam for all your help.

Regards
Alan