Conditional Format

  livewire 16:13 24 Nov 2004

hi-ho all!

I have another sticky question. :(

would like to have a Hotel Double Booking check thingy.

To begin, I was wondering if in Excel i could set up a conditional formatting rule (ie. red text)if that if for example:
Name Check in Out

1 Mr Pc Advisor 12/04/04 14/04/04
2 Mr Fourum 13/04/04 16/04/04

As you can see the above is incorrect and would cause a double booking on this service. how could I set up either an Access Database or Excel Spreadsheet to work out that if there is a double booking it con show me either with an error (In access) or red text(in excel)

If ANYONE has any ideas I would be so grateful to hear them! Please!


Please help me unstick my situation and make my day :) happy one.

Thanks in advance,


  Simsy 17:02 24 Nov 2004

in Excel, it can probably be done, but it really depends on how the spreadsheet is made up.

I can't quite get my head round it from the example you have given.

It may be that a better way it to redesign the Excel sheet, (assumming it does already exist?), such that you can't actually make a double booking.

However, though I am not an expert in Access, (or any database), my gut reaction is that Access is probably a better tool for this task, so I'll leave it to others!

If the Excel sheet does already exist, if you can provide a better description of the layou I'll have a think.

Good luck,



  livewire 17:26 24 Nov 2004


Thanks for your reply. I am yes trying to use Acess now, but it is becoming rather difficult. the database did not pick up the invalid dates :(
Take a look at the image below:

click here


  recap 17:43 24 Nov 2004

In Excel you could use the IF function:


  livewire 17:55 24 Nov 2004


That is a great function! Super!

There is just one more problem:
recap, look at this image:

click here

-This should be marked as 'warning' becuase it is infact a double booking of one room becuase the first customer is in the room from the 11/06/2004 to the 15/06/2004. So 12/06/2004 is invalid.

Do you know how to get around this one?

You have been a big help! :)

  recap 18:27 24 Nov 2004

At the moment livewire I have gone brain dead, but I think you may have to include a "Range" in the formula. Unless somebody knows an easier way?

  Simsy 19:15 24 Nov 2004

but from the first picture you link to, (which I think is in Access), I get a better idea.

Assuming the same layout in Excel, what is the difference between "Room Number" and "Number of Room"? Is this not the same thing?

I think something can be done in Excel, but I still have a feeling Access would be better.

I will give it some thought, though I don't have much time at the moment.

Good luck for now.



  livewire 19:18 24 Nov 2004


Yes, you are right number of rooms will be removed from the table...

  smudge101 19:31 24 Nov 2004

How many rooms are we looking at in total?
and how many are single double or twin?
I take it that you only want to show a doulbe booking error if you have no rooms of a particular type left on any day of the booking and display where the double booking is?
If this is the case you will be much better off with a database such as access as the problem can be solved programatically.
Your image of the access table is not really relevent as you would want to have forms to enter the data into with the 'program' driving the database in the background. If you let me know the other information you want in the database I will have a look at mocking something up for you but it may take a few days as I only manage to get an hour at a time.

  livewire 19:39 24 Nov 2004

No Probs. Thanks for you help!

Well, all I need is a database which prevents double bookings. I am able to create the froms macros tables and queries etc. But the Index rule for preventing double bookings has confused me. If you could just make a breif database table which can prevetn double booking in and between a Check in and check-out that would be most useful.

Once I have that structure I can do the rest!

Super thanks!!

  smudge101 19:47 24 Nov 2004

Which version of access are you using so I can make it compatible?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Print designer Kelly Anna on confident mark making & modern femininity

New iMac Pro release date, UK price & specs rumours

Comment créer, utiliser et supprimer son compte Facebook ?