Excel 2007 : Time Formula

**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

Excel 2007 : Time Formula

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions