Help needed with Formula

[Bryan]

I am hoping what I want to achieve can be done. I am not a speadsheet
expert so hope someone can help me.
I have a column of times in 24 hour format. I have cells to the right of
that covering monday to sunday. I want to put a time in 24 hour format that
looks to the time set in the column of times that says, for example. The
time in cell B 9 is 12:01, the time of 12:20 has been entered in H 9. This
is greater than B 9, convert the difference to minutes and insert into H 9
on a different sheet. If the time is identical or before the time in B 9
insert a 0 in H 9.
If it helps I can email the spreadsheet to who ever can help.

Thanks.

 

[T. Valko]

Try this on the other sheet:

=IF(COUNT(Sheet1!B9,Sheet2!19)<2,0,MAX(0,Sheet1!H9-Sheet1!B9))

Format the cell as TIME 13:30

Biff

 

[T. Valko]

Typo:

=IF(COUNT(Sheet1!B9,Sheet2!19)<2,0,MAX(0,Sheet1!H9-Sheet1!B9))

Should be:

=IF(COUNT(Sheet1!B9,Sheet2!H9)<2,0,MAX(0,Sheet1!H9-Sheet1!B9))

Just to add a little...

If you want the result to be expressed as decimal minutes - 1:00 (1 hr) = 60
(decimal)

=IF(COUNT(Sheet1!B9,Sheet2!H9)<2,0,MAX(0,Sheet1!H9-Sheet1!B9)*1440)

And format as GENERAL or NUMBER

Biff

 

[T. Valko]

Don't worry. I'll get it right one of these times!!!!

Argh!

Typo:
=IF(COUNT(Sheet1!B9,Sheet2!19)<2,0,MAX(0,Sheet1!H9-Sheet1!B9))
Should be:
=IF(COUNT(Sheet1!B9,Sheet2!H9)<2,0,MAX(0,Sheet1!H9-Sheet1!B9))

Really, should be:

=IF(COUNT(Sheet1!B9,Sheet1!H9)<2,0,MAX(0,Sheet1!H9-Sheet1!B9))

Biff

 

[Bryan]

This is so close to what I need. Everything was working fine till I
realised I need to account for times after midnight. If 2300 is the time
in B 9 and I enter 0030 in H 9 I get a 0. The answer I am looking for is
90.

 

[T. Valko]

I need to account for times after midnight.

That presents a problem.

You said if the time in H9 <= B9 then return 0.

23:00 - 0:30
23:00 - 22:45

How do we know which one of those (if either) should be 0?

The 0:30 obviously spans past midnight but how can you tell whether or not
22:45 spans into the next day?

Biff

 

[Bryan]

That presents a problem.

You said if the time in H9 <= B9 then return 0.

23:00 - 0:30
23:00 - 22:45

How do we know which one of those (if either) should be 0?

The 0:30 obviously spans past midnight but how can you tell whether or
not 22:45 spans into the next day?

Biff

Just to explain what I am after, which might help you understand what I am
doing. The time in b9 represents the scheduled arrival or departure of a
train. If the train arrives or departs early, for management measuring
purposes then that is fine. The 0 result is what I wanted. I am only
interested in measureing in minutes any trains that arrive or depart late.
So for a scheduled train arrival of 2300 where the train doesn't arrive
till 0030, then that is 90 minutes late. Your formula works great for all
other scenarios except when a time spans past midnight. I have my fingers
crossed for a fix for this.

 

[T. Valko]

You need some way to distinguish whether or not when H9 < B9 does H9 span
into the next day.

The easiest way to do that would be to include the date with the time:

5/1/2007 23:00 - 5/2/2007 0:30
5/1/2007 22:30 - 5/1/2007 22:45

=IF(COUNT(Sheet1!B9,Sheet1!H9)<2,0,MAX(0,MOD(Sheet1!H9-Sheet1!B9,1)*(Sheet1!B9<Sheet1!H9))*1440)

Biff

 

[Bryan]

You need some way to distinguish whether or not when H9 < B9 does H9
span into the next day.

The easiest way to do that would be to include the date with the time:

5/1/2007 23:00 - 5/2/2007 0:30
5/1/2007 22:30 - 5/1/2007 22:45

=IF(COUNT(Sheet1!B9,Sheet1!H9)<2,0,MAX(0,MOD(Sheet1!H9-Sheet1!B9,1)*(Sh
eet1!B9<Sheet1!H9))*1440)

Okay, not sure if I entered the date and time correctly, but I get the
answer I want. I formated the cell with d/mm/yyyy hh:mm. How can I hide the
date part of the formula?

 

[T. Valko]

Okay, not sure if I entered the date and time correctly, but I get the
answer I want. I formated the cell with d/mm/yyyy hh:mm. How can I hide
the
date part of the formula?

Suppose you enter in B9:

5/1/2007 23:00

Format the cell as TIME 13:30 and it will only display the time 23:00 but if
you look at it in the formula bar it will show the date and time.

The formula cell should be formatted as GENERAL or NUMBER 0 decimal places.

Biff

 

[Bryan]

You need some way to distinguish whether or not when H9 < B9 does H9
span into the next day.

The easiest way to do that would be to include the date with the time:

5/1/2007 23:00 - 5/2/2007 0:30
5/1/2007 22:30 - 5/1/2007 22:45

=IF(COUNT(Sheet1!B9,Sheet1!H9)<2,0,MAX(0,MOD(Sheet1!H9-Sheet1!B9,1)*(Sh
eet1!B9<Sheet1!H9))*1440)

Entering the date and time in, in this case B21, and formating as time
seems to break the formula. 8-(

 

[T. Valko]

Entering the date and time in, in this case B21, and formating as time
seems to break the formula. 8-(

Here's a small sample file that demonstrates this.

Time.xls 13.5kb

http://cjoint.com/?fctko4kIFO

Columns B and H are formatted as TIME 13:30

The formula is in column J and is formatted as GENERAL

As you will see, only the TIME is displayed in the cell. If you select a
time cell and look in the formula bar you will then see the date and time.
The formatted value is not the true underlying value of the cell. The true
value of the cell is what you see in the formula bar.

It's easy for you and I to look at these times and know one spans midnight:

23:00 - 0:30
23:00 - 22:45

A computer can't see that like we do. They don't have brains! As far as the
computer is concerned both 0:30 and 22:45 are less than 23:00 and both
*could* be in the next day. So, we have come up with some means of letting
the computer know that some times span into the next day. The *BEST* way to
do that is include the date.

Biff

 

[Bryan]

http://cjoint.com/?fctko4kIFO

Thanks, your file alerted me to what I was doing wrong. I wasn't advancing
the date in the cells to the right of B21,to show the next day, so as you
pointed out, the computer isn't clever enough to guess what I wanted. Well
the problem is almost fixed now. Everything adds up fine, but, at the end
of the week when the data is deleted from the spreadsheet the date format
is removed as well. As I aren't the only one to be using this file I have
to keep it as simple as possible, not everyone is computer literate. Unless
you can offer a solution to this I think I can come up with a macro kludge
to get around this. Thanks for taking the time any way to help me out.

Bryan

 

[T. Valko]

Thanks, your file alerted me to what I was doing wrong. I wasn't advancing
the date in the cells to the right of B21,to show the next day, so as you
pointed out, the computer isn't clever enough to guess what I wanted. Well
the problem is almost fixed now. Everything adds up fine, but, at the end
of the week when the data is deleted from the spreadsheet the date format
is removed as well. As I aren't the only one to be using this file I have
to keep it as simple as possible, not everyone is computer literate.
Unless
you can offer a solution to this I think I can come up with a macro kludge
to get around this. Thanks for taking the time any way to help me out.

Bryan

Having to include the date is a PITA but you have to do something!

As far as "deleting" all the old data, I have the "clear contents" button on
my formatting toolbar. It looks like a chalkboard eraser. It'll clear the
cell contents but won't remove any formatting.

Biff