Working with times
 

I just wondered if it was possible to check time availavilty within a
spreadsheet.

For example. I have the following data
Date Start Time End Time
5/2/03 09.00 11.00
5/2/03 13.00 15.30
5/2/03 15.00 16.00

For the 3rd entry I would like to display an error as there is already a
meeting scheduled for this time.

I have asked this before, but maybe wasn't very clear in what I required, as
I had no responses. If there is no way to do this, many thanks for taking
the time to read this post. If there is possbly another way I could work
this please let me know, any help would be greatly appreciated.

Many Thanks

Working with times
 

In this set up you can add a formula in cell D3 as follows:

=IF(B3="","",IF(B3<C2,"ERROR",""))

and copy it into D4 (and downwards). I have assumed you have a row of
headings, so you actually want to check from the second entry onwards.

Hope this helps.

Pete

Working with times
 

Put this is the second data row and copy down

=IF(OR(SUMPRODUCT(--($B$2:B2<=B3),--($C$2:C2B3))0,SUMPRODUCT(--($B$2:B2<=C
3),--($C$2:C2C3))0),"Error","")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Pendelfin" wrote in message
...
I just wondered if it was possible to check time availavilty within a
spreadsheet.

For example. I have the following data
Date Start Time End Time
5/2/03 09.00 11.00
5/2/03 13.00 15.30
5/2/03 15.00 16.00

For the 3rd entry I would like to display an error as there is already a
meeting scheduled for this time.

I have asked this before, but maybe wasn't very clear in what I required,
as
I had no responses. If there is no way to do this, many thanks for taking
the time to read this post. If there is possbly another way I could work
this please let me know, any help would be greatly appreciated.

Many Thanks

Working with times
 

Assuming you have dates in column A from A2 down and the start times are
from B2, end times from C2

you could use this formula in D2 and copy down column

=IF(SUMPRODUCT(--(A$1:A$10+B$1:B$10<A1+B1),--(A$1:A$10+C$1:C$10A1+B1)),"oops!","")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506994

Working with times
 

I have tried that formula, but it appears to show "oops!" in valid times, and
not in invalid ie the 8/1 doesn't show oops?

Please advise.

Date Start End
01-Jan 09.15 10.00
02-Jan 16.00 17.00 oops!
03-Jan 09.00 15.15
03-Jan 14.00 16.00 oops!
05-Jan 12.00 13.00 oops!
06-Jan 09.00 11.00 oops!
06-Jan 17.00 18.00
08-Jan 11.00 11.30
08-Jan 11.00 11.30



"daddylonglegs" wrote:


Assuming you have dates in column A from A2 down and the start times are
from B2, end times from C2

you could use this formula in D2 and copy down column

=IF(SUMPRODUCT(--(A$1:A$10+B$1:B$10<A1+B1),--(A$1:A$10+C$1:C$10A1+B1)),"oops!","")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506994

Working with times
 

Bob

I tried the following formula but I have errors on Valid rows.

Please help.

Date Start End Time Check
01-Jan 09.15 10.00
02-Jan 16.00 17.00
03-Jan 09.00 15.15 Error
03-Jan 14.00 16.00 Error
05-Jan 12.00 13.00 Error
06-Jan 09.00 11.00
06-Jan 17.00 18.00 Error
08-Jan 11.00 11.30

I would only expect to see the error on rows 3 and 4.
I have entered this into cell d2 and copied this down is this incorrect?

Many Thanks

"Bob Phillips" wrote:

Put this is the second data row and copy down

=IF(OR(SUMPRODUCT(--($B$2:B2<=B3),--($C$2:C2B3))0,SUMPRODUCT(--($B$2:B2<=C
3),--($C$2:C2C3))0),"Error","")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Pendelfin" wrote in message
...
I just wondered if it was possible to check time availavilty within a
spreadsheet.

For example. I have the following data
Date Start Time End Time
5/2/03 09.00 11.00
5/2/03 13.00 15.30
5/2/03 15.00 16.00

For the 3rd entry I would like to display an error as there is already a
meeting scheduled for this time.

I have asked this before, but maybe wasn't very clear in what I required,
as
I had no responses. If there is no way to do this, many thanks for taking
the time to read this post. If there is possbly another way I could work
this please let me know, any help would be greatly appreciated.

Many Thanks