Subtracting times.

[derek]

Excel 2000
Hi need some help on a time function please.
Column A is formatted as time, h:mm, as users dont want to
get involved with dates.
Column B is formatted as number, one decimal place.

A1 contains a start time,which could be anytime. User
inputs a time of an event in A2, again in A3 etc. etc.
over a period of 24 to 72 hours. I would like in B2, B3
etc.etc. the time period from the start time to the
event. In column B i'm using the formula 24*(A2-$A$1)
which works fine upto the first midnight when I then have
to change the formula to 24*(A2-$A$1+1), then the next
midnight I have to change it to +2, then +3.
Can someone help me out with a correct function that
doesn't need manual manipulation! please.

TIA, derek

 

[Tom Ogilvy]

In B2 put in the formula
=((A2-$A$1)+SUMPRODUCT(($A$2:A2<$A$1:A1)*1))*24
Then drag fill down the column.

 

[Tom Ogilvy]

Here is another way

in B2
=(A2-A1+(A2<A1))*24+B1

Then drag fill down the column.

 

[Wild Bill]

If you span days then you really need to design date in column A - the
users should understand that. For simplicity you could have them put
number of days in an inserted column A, or 3 day DOW abbrev., or provide
a listbox with the 7 weekdays. (But then could you span > 7 days? It
gets back to design.) Then you might as well use DATEDIF anyway,
recognizing that there will be an error message if they enter a date
earlier than the start.

Failing that, if the span will never be more than 24 hours, just use IF
comparing entered time to start time, reacting to negative results.

None of these require programming. I suspect you don't really want a
programming solution, but if so it's to use DateDiff. DateDiff will
respect negative time spans, so you'll have to adjust appropriately via
IF or IIF.