Creating a calendar between 2 variable dates to a specific format

**Jinxedcookie** · 06-30-2012, 07:10 PM

Hello all

I'd really appreciate your help. I have a database (attached) to which I would like to have a mini calendar included so that I can make notes as to what happened on certain days. My problem is that the number of days for each worksheet will vary. The startdate (B6) and the end date (B78) need to be included along with the day of the week underneath (that bit I can do!) and it needs to go along a row.
The idea of what I would like is shown in A36:D53. I would also like the formatting to be included (does not have to be those colours, just as long as you can see the text and they are different) Not asking much am I. I found something for autofilling in dates for a column but not for a row and I really do not have the knowledge to try to code this myself.

Thanks for your time

Helen

**Russell Dawson** · 06-30-2012, 10:41 PM

Hi Helen,

I'm not sure whether I fully understand what you mean but I have tried to put myself in your shoes and with a bit of reading between the lines I have come up with the attached. (see lines 36 to 43)

I've done a little reformatting to make it clearer to the user and added what I think could help you. I've created a daily record (not really a calendar) that will start on the date of admission in B6 and automatically input dates and days as I think you need. It will add 55 days but you can copy the cells across to add to that. Beneath there is space to make a very short record of the event but you can then elaborate with the addition of "Comment" box. I've also included Data validation message to prompt that.

Taking thing a little further I have created a new sheet that would list all the data vertically which I think is a better way of displaying your data and is more user friendly IMO.

Apologies if I have gone too far, feel free to discard any or all of my suggestions. Got a bit giddy, insomnia on a quiet Sunday morning and nothing on the telly.

I hope some of my suggestions work for you.

Cheers

Russell

**Jinxedcookie** · 07-01-2012, 05:41 AM

Thanks for your efforts Russell!

I'm sorry I did not explain very well, but you have come up with a good interpretation for what I meant. I see how having comments can be useful, but in fact the information that I would put in the comments box is usually important and would need to be able to copied to another worksheet in the future, and I cannot see a way to do that without copying and pasting each comment. I also like the vertical layout, but the reason I chose a row layout is for my colleague as I think it would suit him better (for most sections it fits on the screen), and also I need to combine worksheets to make a larger database and I found a tool I could use to do that.

What I was hoping for was a nifty bit of code that would look at B6 and b78 and then just add in the dates and days between (and including start date and end date) starting at b36 and format the 17 lines beneath into 5 different colours. I need code as sometimes there are more than 20 days and again I want a simple hit a button solution for my colleague.

Thanks for bearing with me

**Russell Dawson** · 07-01-2012, 06:04 AM

Hi Helen

Would you like me to ask for VB help on this?

Russell

**Jinxedcookie** · 07-01-2012, 12:34 PM

Hi Russell

Yes please if you would

Thanks

Helen

**Marcol** · 07-01-2012, 03:52 PM

I can't see why you need VBa as yet.

Your problem isn't very clearly explained, the cell refs don't seem to match your workbook.

Try this carve up to get the ball rolling again
Enter the admission date in Sheet2!B6, the duration in days will show in E6, and the calendar will automatically populate as the days go by.
When the date is added to Sheet2!B67, the calendar will stop growing.

I've put the calendar on Sheet1 for clarity.

P.S.
Is "InfoBasis" a design consultancy, Ms Cooke?

**Jinxedcookie** · 07-01-2012, 05:10 PM

Well that's a cunning way to fill a calendar - that works well for what I want. I'm going to see if I can adapt it, otherwise I may be back asking for more help!

Thank you Marcol

PS Infobasis is a defunct business and never was a design consultancy - not sure how you discovered that as I far as I know when I bought Office 2010 my name was entered. Obviously Infobasis still lurks in the ether