Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    freezing a date (2000+)

    Hi All,

    In a template, I have a formula for a cell that derives the nearest Wed, which corresponds to the end of our work week, based on the date returned by the PC via the BIF NOW(). On Thur of the week (the day after the work week ends) or, at the latest, on Fri of that week, people are supposed to open a new file based on that template and fill in their time worked. Of course, they then save the file. Accounting usually looks at the file by Fri of the "real" week to do whatever they do.

    I also allow an override of the Excel-generated Wed date in case, for example, they were on vacation and they're filling out a timesheet for a few weeks ago.

    Is there some way to "freeze" the date when the file is created? If accounting doesn't get to their job in time, then the formula used to derive the date may return the following week's Wed. I certain don't want people to do a paste-values operation.

    To illustrate: for now, I've settled on an approach that says if "today's" weekday is 4 or more (Wed-Sat), then the Wed ending the work week is the day you're in (Wed) or the previous Wed (if you're in Thur-Sat). If you're on a Sun-Mon, then the Wed ending the work week is the upcoming Wed. It's not particularly important which weekdays correspond to which Wed (past or upcoming) and that might change. For example, the formula might be something like:
    =if(weekday(now())>=4, now()-mod(weekday(now()),4), [false result])

    But once a file based on the template is created, I don't want the "week ending" date to change. Maybe I need a document property (although I'm not sure how I'd do that).

    Any ideas?

    TIA

    Fred

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: freezing a date (2000+)

    You could add some code to workbook_open:

    Private Sub Workbook_Open()
    If Me.Path="" Then
    'Write date to a cell because file has been opened from template and hasn't been saved yet
    Else
    Msgbox "Please open a fresh file from the template!!!!"
    End if
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: freezing a date (2000+)

    If that formula is in cell A1 on sheet Sheet1, then the following code, placed in the workbook open event routing in the module behind the ThisWorkbook object will convert the formula into its value when the workbook is opened.

    <pre>Private Sub Workbook_Open()
    If Worksheets("Sheet1").Range("A1").HasFormula Then
    Worksheets("Sheet1").Range("A1").Value = Range("A1").Value
    End If
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: freezing a date (2000+)

    Legare,

    I'm not sure I understand where the workbook_open routine would go: it must be in the template since the workbook doesn't exist until I do File | New. And when I do File | New basing it on the template, am I opening a workbook?

    I'd ask if there was any way to do this without using VBA but I'll bet there isn't since you'd probably have mentioned it.

    I just want to make sure that the rqmts were clearly specified:
    - I have a template with a formula, say in cell xx of Sheet "1 of 2" (that's the actual name of the work sheet for entering your time for the week) that can calculate a week-ending date as either the previous Wed or the next Wed, depending on which day of the week I'm on
    - I have to allow a manual override, perhaps in cell yy, in case someone is filling in a time sheet for several weeks ago (eg, just back from vacation or jury duty)
    - Now I open a new workbook based on the template so the workbook has the dates in xx and yy per the template
    - once the workbook is saved, the week-ending date, perhaps in a third cell zz, cannot change in case some looks at the workbook in a few days or a few weeks
    - preferably no VBA at least in the workbook and preferably not even in the template (is that possible?)

    I purposely used 3 different cells (xx, yy, zz) because they contain logically different info. Maybe this can be reduced to 2 or even 1 but I'm not sure I see that. If I can't do this without VBA, I might be better with a small user form for the functions in the xx and yy cells (altho I'd not want the user form and its code to be in the workbook). If so, could you get me going on this since I've never done anything with a user form.

    Thanks.

    Fred

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: freezing a date (2000+)

    Jan Karel,

    Thanks.

    I think I'd leave the else part out. If someone opens the workbook after it's been created, they'd get the msgbox. One might open the workbook after creation for several reasons.

    See also response to Legare for other comments.

    I see you got a new version of GoBack out. I downloaded it but haven't had a chance to test it. It sounds like there's some big changes.

    Fred

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: freezing a date (2000+)

    What you are asking can not be done without VBA. If you create a formula that includes the NOW() function, that cell will be updated every time the workbook is opened. The only other alternative would be a manual copy and paste special values to eliminate the formula.

    Yes, you would put my code in the workbook open event routine for the template. The workbook open event routine is in the module that is behind the ThisWorkbook object. To find this routine, go to the VB editor. In the Project Explorer, right click on the ThisWorkbook object. This should open the module in the code window. Find the left drop down list at the top of this window and drop it down. Select Workbook from the list. If the right drop down list does not display "Open", then drop down the list and select Open. You should now have a dummy Workbook_Open event routine in the code window. Replace this routine with the one I posted. My code will need to be modified to use your sheet name and cell address.

    I do not know why you would need more than one cell. The code I posted will replace your formula with the date it calculates when the new workbook is created from the template. That cell then becomes just a cell with a date in it. If the user needs to override the calculated date, he can just type the overide date into the same cell. Am I missing something here? I don't see anything different about the dates, logically or otherwise.

    If you want to prevent the date from being modified at a later time, then you will need to put in some additional VBA code to protect the worksheet. This would most likely be done in the workbook before save event routine. That would allow the date to be changed up to the time the workbook is first saved.
    Legare Coleman

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: freezing a date (2000+)

    Not sure if the published version already has it, but I have been working on introducing a small delay time in the utility, so when you press and hold a navigation key, not all addresses are remembered, just the one you pause in long enough. So if you're scrolling down, not all rows are remembered. Likewise, if you have many worksheets and you hold control-pagedown to get to a worksheet for to the right, it just stores the one you end up in.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: freezing a date (2000+)

    Hi Legare,

    >I do not know why you would need more than one cell.

    You're not missing anything. It's me who didn't realize that your workbook open routine was replacing the formula with its value so that the cell now had a constant. So if the date is wrong, they put in another date as a constant. If accounting or someone else opens it a week or two later, its still a constant. So there's no need to even protect the cell.

    Thanks.

    FRed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •