Date Model Worksheet

How about something like the attached file?

Maybe this will kick start your solution...

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

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.

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:

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

Select allOpen in new window

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.

Hi,
As attached. Just run the macro and it will complete the fields for you.
Regards

Auto-Date-Format-Worksheet-Updat.xlsm

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.

Sure, updated to delete existing structure and re-apply.
Regards

Auto-Date-Format-Worksheet-Updat.xlsm

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.

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

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.

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,

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.

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,

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.

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

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.

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

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.

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

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.

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