?
Solved

Date Model Worksheet

Posted on 2011-11-01
22
Medium Priority
?
284 Views
Last Modified: 2012-05-12
EE Professionals,

Greetings!  I am trying to put together a worksheet where based on two dates (start/finish), the worksheet will automatically create a set of predefined cells (representing days between the dates) with the appropriate numerical date as a heading with a appropriate mouth above that.  I have attached a mock up to show the concept.  

Thank you in advance,

Bright01
Auto-Date-Format-Worksheet-Examp.xlsm
0
Comment
Question by:Bright01
  • 11
  • 10
22 Comments
 
LVL 2

Expert Comment

by:LlanoV
ID: 37062171
How about something like the attached file?

Maybe this will kick start your solution...

Cheers
Llano
Auto-Date-Format-Worksheet-Examp.xlsm
0
 

Author Comment

by:Bright01
ID: 37062299
Llano,

Thank you for the reply and sample.  If you put in START DATE of 2/1/2012 and END DATE of 2/15/2012, the model does not readjust.  The idea here is that once you establish the Time Horizon for a project, a macro will automatically adjust the model and include the neuances of dates (i.e. some months have 30 days, some have 31 days and Feb. has 28 days -- etc.).  I think what you have is a good start....but how do I get it to adjust properly?

Thank you,

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37062325
Bright01,
If you create a new module and add the code below this will do most of the job for you, based on the following assumptions:
you have defined (named) the cells containing the start and end dates as "startDate" and "endDate" respectively
you require the rows and initial columns as per your sample

This will also group by month but I haven't grouped any lower down i.e. by week, nor provided the collapse/expand code. The first needs to to be more explicit as to when your week starts i.e. Monday, Sunday or whatever, the second should be easy enough to add anyway.
Regards

 
Sub fillForDateRange()
    Dim dateCount As Long
    Dim n As Long
    Dim IsGrouped As Boolean

    ' remove any existing grouping ==>
    On Error Resume Next
    Do While Not IsGrouped = (Cells(6, 5).Columns.OutlineLevel > 1)
    Columns.Ungroup
    Loop
    On Error GoTo 0

    dateCount = DateDiff("D", Range("startDate"), Range("EndDate")) + 1
    Cells(6, 4) = Range("startDate")

    For n = 1 To dateCount
        ' throw in the dates ==>
        Cells(7, n + 3) = Day(Range("startDate") + n - 1)
        ' add a month header ==>
        If Cells(7, n + 3) < Cells(7, n + 2) Then Cells(6, n + 3) = DateValue(Range("startdate") + n - 1)
        ' check month size and center across ==>
        If Cells(7, n + 3) < Cells(7, n + 2) Then
            With Range(Cells(6, n + 3), Cells(6, n + 3).End(xlToLeft))
                .HorizontalAlignment = xlCenterAcrossSelection
                .NumberFormat = "mmm/yyyy"
            End With
            ' group selection ==>
            Range(Cells(6, n + 2), Cells(6, Cells(6, n + 3).End(xlToLeft).Column + 1)).Columns.Group
        End If
        ' repeat for final group to tidy up
        If n = dateCount Then
            With Range(Cells(6, n + 3), Cells(6, n + 3).End(xlToLeft))
                .HorizontalAlignment = xlCenterAcrossSelection
                .NumberFormat = "mmm/yyyy"
            End With
            ' group selection ==>
            Range(Cells(6, n + 3), Cells(6, Cells(6, n + 3).End(xlToLeft).Column + 1)).Columns.Group
        End If

    Next
    'and autofit ==>
    Range(Cells(6, 4), Cells(7, dateCount + 3)).Columns.AutoFit
End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Bright01
ID: 37062875
SA,

Thanks for the code!  I tried to put it into the spreadsheet (new module) that I originally had posted but couldn't get it to work.  Can you send me a clean workbook with the code you have recommended?

TY,

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37063168
Hi,
As attached. Just run the macro and it will complete the fields for you.
Regards

Auto-Date-Format-Worksheet-Updat.xlsm
0
 

Author Comment

by:Bright01
ID: 37063266
SA,

Thanks for the update.  When I test it with different dates, it doesn't clear the previous settings.  So if you go from 12/1/2011 to 2/28/2012 and then switch it to 12/1/2011 to 1/15/2012, you get a run on list of numbers and an inaccurate date in the month (merged) field.  Hopefully this is a minor issue to resolve.

Much thanks,

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37063418
Sure, updated to delete existing structure and re-apply.
Regards

Auto-Date-Format-Worksheet-Updat.xlsm
0
 

Author Comment

by:Bright01
ID: 37063729
SA,

Great!  The dates work; however, the Day/Week/Month collapse doesn't work.  It does collapse but doesn't show any headings.  If not an easy fix, I'll pose it as another follow on question.

Please advise,

Thank you,

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37063983
Bright01,
As I mentioned I didn't add any code for the expand/collapse as that requires a couple of things from you:
1) what day do you want your week to start?
2) what do you want in the headings? Week number? Month/Year?
Also, yes it is more complex but that's not necessarily a problem. To be able to collapse by week/month may require a second set of grouping to be added. It would help to know more about what you want to use this for (but without you having to provide confidential data), as the code I've provided is rough and ready, rather than polished and fit for any purpose.
Regards
0
 

Author Comment

by:Bright01
ID: 37064115
SA,

Thanks for the note.... you are right...you did mention that before....my mistake.

1.) Week should start on Monday, end on Friday
2.) Week number, Month/Year would be great

As for where I'm headed with this, I have a fairly complex RACSI Model that has evolved but is not correctly designed for getting it to a day level (right now it's at a week only level).  I broke down the project to concentrate on being able to set a Project Horizon and then begin to populate the model based on specific tasks, etc.  The part you are working on becomes the basis for the Project Horizon.  I plan to send out a follow on question as soon as I integrate the Project Horizon with the rest of the model with some minor tweeks.  Make sense?

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37064164
Ah, expanded RACI. All clear.

So, based on what you say above, can I assume that you actually only need working days (Mon-Fri) for your model, as opposed to the 7-day model that was indicated in your dummy file? That, of course, is also possible but will impact on the week collapse/expansion set-up.
Regards,
0
 

Author Comment

by:Bright01
ID: 37064199
Well, the reason I had left in the weekends is 1.) often many of us work weekends ;-) and 2.) I thought it would make it easier in calculating the dates at the day level.  Also, if I exclude the weekends, I thought I would need some error checking to notify the person entering the date that they have choosen a weekend to begin a task.  My objective is not to recreate MS Project; but to provide a very simple project mgmt. capability that stresses roles and responsibilties and ties out with the RASCI or RACI model.  By putting in some cleaver macros, it makes it easy for a consultant to use it.

As for the design, weekends vs. non-weekends, I'd probably go with what you think makes most sense and is easy to build in.

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37064545
Okay, first thoughts on this would be:
1) no problem if you want to work weekends; I'm an Excel developer by trade, and work when I want provided I get the job done by the agreed deadline and for the agreed price :-) ;
2) you need to decide on how you want to standardise your weeks, and how to get Excel to work with them. For instance we can throw in a custom function to calculate week numbers but then this brings up another question or two i.e do you want:
a) the week number in that particular month, so you will only be working with weeks 1 to 5 recurring;
b) the week number in the year, so you will be working with 1 through 52.
 
If the latter then you also need to decide how you want to work out the week number. The easiest way is to use ISO week numbering, but this does throw the issue that the first few days in any year may fall under week 52 of the previous year using ISO week numbering e.g. for 01 Jan 2012 falls on a Sunday and so is actually the last day of 2011 week 52, with 2012 week 1 beginning on Monday 02 Jan 2012. If this is not enough of an issue for you to worry about then we just need to throw in a function to handle that and we're set to go with creating the week/month groupings and to have an expand/collapse function for that.

And, of no real excitement, do you also want to include the day name in a 3rd row so that end-users can see whether it's a Monday etc?
Regards,
0
 

Author Comment

by:Bright01
ID: 37064659

1. would be great
2. I'd say work with 1-5; I don't think using the 1-52 is enough of an issue.......
3. The day name would be great!

Thanks for helping define all the options here....again appreciate your time on this.

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37065225
B,
Shame about not using week 1 to 52 - was just having a play while waiting on a call from a client and threw something together for you to look at - have a look at the attached.

Note that if you'd still rather have weeks 1 to 5 then I'll give you the code for that since it's entirely up to you (your question, not mine). Note that I've thrown on a couple of toys and moved things around slightly but the general idea is the same. To enter a start and end date just click on the relevant cells - each will bring up a calendar selection for you to use. The code for these is clean and is fully available out there in the good ol' internet (although I think I set it to UK format so if you want to keep that bit then I'll provide you with a US format version if req'd).

Let me know your thoughts, as I know this may be getting a bit far from your original question.
Regards.

Auto-Date-Format-Worksheet-Updat.xlsm
0
 

Author Comment

by:Bright01
ID: 37065522
SA,

WOW!  This is really good!  One problem, check the calendar function (way cool) -- check the drop down box.... and if you put in a selection, it's putting in the wrong year.  Can I access this via the Developer's Tab?

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37065592
B,
Yes. If you use Ctrl+F11 to get into the VBE then the code is all in the form fCalendarFrm. I have assumed that you're in the U.S., and if so this is is why you get funny results. If you do want to look at it then the relevant code is in the code for the form under the routine "Private Sub Build_Calendar", else I'll be back online tomorrow and will pick up for you from there.
Regards
0
 

Author Comment

by:Bright01
ID: 37065667
SA,

I am in the U.S.  Go to the form and use the drop down.  You will see it has January repeated instead of January, Febuary, March, and so on.......

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37065771
Odd, had it do that before (and I use it a lot for clients). I'll have a look shortly, although I do think it's the UK/US issue I already mentioned. Will let you know once I've finished dinner (hooray for EE mobile).
Rgds
0
 
LVL 7

Accepted Solution

by:
philip m o'brien earned 2000 total points
ID: 37069341
B,
Meant to say "Haven't" in that one. Nevertheless it will still be related to the US/UK re-coding. I had a quick look it this morning and, as I wasn't able to replicate the problem I have provided a different calendar which hopefully won't have the same issue for you. This will open in the same way, simply be clicking on the two designated cells, but should handle international date formats better.

There are further limitations on your original request, vis-a-vis expand/collapse by week/month. You will be unable to break it down by week i.e. have separate groupings unless you also add in a breaker column between each week set. This is actually the same reason why the monthly groupings I provided yesterday group all but day 1 of each month.

Rather than worrying about that for now I instead added a routine to provide alternate colouring by week number.

For the expand/collapse there are several ways to do this, and the easiest is probably to have a spinner control and simply expand/collapse based on the spinner value, but I wasn't sure how familiar you were with forms vs activeX controls so haven't done anything with this.

If you want something along those lines just let me know, although I don't know whether I'll have much time spare this afternoon it should be a quick issue to deal with.
Regards,

 AutoDateFormatWsUpdate2.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 37069422
SA,

Great work!  You've done a fantastic job in over achieving on this original question.  You over achieved my expectations on dealing with over 19 itterations..... unbelievable.....and you continued to make great recommendations and enhancements.  It was difficult to close this out simply because you continued to add new ideas and took this on as a commitment to enhance it.  I'll be asking a related question but I want to clost this out because you deserve the points and more.

Much thanks,

B.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 37069535
Not a problem at all, I'm a bit obsessive at times so I was hoping I'd not strayed too far from your first brief.

Oh, and my best wishes that your project goes well.
Regards,
Philip
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question