Excel Calendar

Hi All,

I have an Excel spreadsheet I use for my small vehicle hire business. In one column I have the date the vehicle is hired from and in another column I have the date the vehicle is hired to.

I was wondering how do I link this to a calendar in Excel so it automatically blocks/shades out the dates the vehicle is on hire for. I currently have 2 vehicles so I would want it that when one vehicle is on hire, it blocks it out on the calendar in one colour and then another colour for the other vehicle.

Please can someone help me with formulas I need etc?

Thanks Emily

I can set this up for you, but it would help if you attached a sample workbook so I can see how your existing data is laid out. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do you have particular names for the cars, rather than car1 and car2?

Pete

Hi Pete,

Thank you for your help. I have attached a template file of how I would like it but I can't see where it is, have you got it?

If you look on the calendar for July and October, you can see where I have blocked out dates in yellow and green, I have done this manually but I would like it so it does it automatically when I input dates on to the 'Burstner' or 'Swift' spreadsheet. Is that possible?

I want to keep it quite simple because none of us are the best on Excel!

Thanks,
Emily

Emily (my daughter's name),

I presume Burstner and Swift refer to the two cars - have you thought abut having the booking details in one sheet, with a column to record which car the booking relates to?

Also, you show individual sheets for each month - you could just have one calendar sheet that covers one month, and you select the month and year from drop-downs. Does this sound okay to you?

Pete

Originally Posted by Pete_UK

Emily (my daughter's name),

I presume Burstner and Swift refer to the two cars - have you thought abut having the booking details in one sheet, with a column to record which car the booking relates to?

Also, you show individual sheets for each month - you could just have one calendar sheet that covers one month, and you select the month and year from drop-downs. Does this sound okay to you?

Pete

Hi Pete,

Yes that would be ok to put them both on one sheet and the calendar on another with all months on. We plan on getting a few more vehicles over the next 12-24 months so it will probably be better to have them all on one sheet.

Thanks, Emily

Hi Emily,

I've been working on this off and on today, but it's too hot to spend a lot of time at the computer - I'll try and get something to you tomorrow.

Pete

Hi Emily,

I've set this up for you in the attached file. I've used one sheet for bookings, and a separate sheet for the calendar.

The bookings sheet is similar to what you had before (from column M), but I've inserted several other columns (coloured blue) to enable the calendar to work. I wont describe every formula in detail (unless you want me to), but essentially they expand the date range in the booking table to have one record per day, and this is categorised by car-type. I've copied the formulae in columns A and C down to row 100, but you should ensure, as you add more booking data, that these are copied at least as far as your data extends. The formulae in columns E to H have been copied down to row 500, as can be seen by the hyphens in column E. As you add more booking data, you should ensure that these are copied down until you start to get hyphens. You can hide these columns if you wish, so that your booking sheet looks as it did before.

You can see in column J that I've also added a list of car-types, where you can have up to 5. This list drives the data validation in column L, where you can choose the car type from a drop-down as you add new data. If you get a new vehicle, just change the name of Car_3, Car_4 etc. in column J.

The calendar sheet is fully automatic, displaying the bookings for up to 5 cars each day and using different colours to highlight those bookings - you can use the drop-downs in K2 and K3 to change the month and year, and the display automatically adjusts.

Hope this helps.

Pete

Hi Pete,

I know! It is very warm isn't it!! Thanks for your help

Emily

Hi Pete,

That looks great, your an Excel genius! Thank you so much, I will have a good look and try and understand it myself!

Thanks for your help, it will be very useful for us!

Emily

I suggest you test it out by putting some made-up bookings in the bookings sheet, with different cars and dates in the same month so you don't have to keep changing things in the calendar sheet, and then check out the results.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Ok I will do that Pete.

I have inputted some of my bookings and added some columns etc. but for some reason on the calendar whenever I put one vehicle out, it blocks out the same dates for car_4? I Can't work the formulas out to change it?! I don't want to be a nuisance but can you have a look please?

Also, may I ask, how did you learn how to do things like this on Excel? Did you do a course? I would love to be able to learn myself as I use Excel quite a lot for work.

Thanks,
Emily

Hi Emily,

sorry about the colours showing incorrectly for Car_4. The colours are generated by conditional formatting and I've corrected them in the attached file, but if you want to know how to do this yourself, you should select cell B9 on the calendar sheet and click on Conditional Formatting | Manage rules, and then you will see there is only one rule which applies to $B$9:$H$9 and it refers to B8 rather than B9. Click on Edit Rule, and then change the B8 to B9, and then click OK twice to exit the dialogue box. You can then repeat this exercise when selecting B15, then B21, B27, B33 and B39 in turn.

I've also applied the colours directly to the list of cars in J3:J7 on the bookings sheet, to act as a key.

As for how I've learnt to do things in Excel, it's a bit like asking "how did you learn to speak English?". In my case, I have a scientific/engineering background, and first started using spreadsheet packages in the late 1980's. I became quite knowledgeable in Quattro Pro, but changed over to Excel in the early 2000's, and in both cases I tended to pick things up from manuals and books, and from the built-in Help files. It was about late 2005 when I first discovered the newsgroups on the internet that were dedicated to Excel, and I just started to contribute to those where I could (Google Groups, Code Cage, Microsoft, Excel Banter etc.). I found that I learnt a hell of a lot by reading other posts that I didn't contribute to, seeing how others were solving problems, and it has just developed from there. Nowadays, I just spend my time on this forum, and still learn new things from it, as well as contributing to those threads where I can.

Thanks for the rep, and for marking the thread as Solved, but if you have any other queries then don't hesitate to raise them here.

Hope this helps.

Pete

That's wonderful! Thank you ever so much for you help Pete, you have been a star

Best Regards,
Emily

Hi All,

New to this forum. I've downloaded one or two of the examples above and have tried to replicate this by autofilling a calendar with classes I would teach on the days and periods I would thus teach them. I can't work out how to do the latter...
If someone could advise, that would be brilliant.

I think I've attached the file... if necessary :)

Richard

Hi Richard,

welcome to the Forum.

Unfortunately, you have broken one of the Forum Rules (which can be seen on the top menu at the top of the screen). Rule 02 says that you shouldn't hijack someone else's thread - start one of your own, with a link back to this thread if you think it is relevant.

Pete