Consulting

Results 1 to 7 of 7

Thread: Need help with date manipulation

  1. #1

    Need help with date manipulation

    Newbie, here. Just gotten started with VBA over the last month or so, so i am still on the not so steep part of the learning curve.

    Here's my problem. I have an excel workbook with a worksheet for each of the 5 weekdays. At the start of each monday i want to get vba to take certain values from the previous friday and insert them into monday after clearing the cells (it is a fresh week after all!!). Part of the problem is that the spreadsheets will be accessed several times each day, so the program will need to recognize that it is a new monday once and then keep track of the dates after that.

    It seems everytime i try to do this, all of my parameters are based on the current date and essentially the process keeps resetting its own baseline.




    Help.

  2. #2
    VBAX Expert
    Joined
    Feb 2005
    Posts
    929
    Location
    Quote Originally Posted by Lostinspace
    Newbie, here. Just gotten started with VBA over the last month or so, so i am still on the not so steep part of the learning curve.

    Here's my problem. I have an excel workbook with a worksheet for each of the 5 weekdays. At the start of each monday i want to get vba to take certain values from the previous friday and insert them into monday after clearing the cells (it is a fresh week after all!!). Part of the problem is that the spreadsheets will be accessed several times each day, so the program will need to recognize that it is a new monday once and then keep track of the dates after that.

    It seems everytime i try to do this, all of my parameters are based on the current date and essentially the process keeps resetting its own baseline.




    Help.
    Hi and welcome to the forum.

    There are many ways to trigger events and then make sure the event does not retrigger until the appropriate next time. Personally, I like to use a "housekeeping" sheet for this sort of thing and keep it hidden most of the time.

    For your case, you want to initiate the cleanup each Monday, so you probably want to add some code in the ThisWorkbook code module, in particular, the Workbook_Open() procedure that:

    1. tests for the day of the week; if Monday, continue, else exit
    2. examines the housekeeping sheet to make sure the cleanup has not already been done for this Monday
    3. if not already done, do the cleanup and update the housekeeping sheet
    Of course, this assumes that the spreadsheet runs every Monday. If it does not, you will need to check each time it runs whether it is Monday or not. Actually, this is a little easier because you simple check the housekeeping sheet to see when the cleanup task last ran. If, this week, then do not run the cleanup task. If last week (or earlier?), then run the clean up task.
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.

  3. #3
    thanks for the input.

    To be honest i couldn't figure out how to do your point #2, which would be very helpful to me. Could you point me to a source or give me a code example on how to do that.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    Quote Originally Posted by Lostinspace
    To be honest i couldn't figure out how to do your point #2, which would be very helpful to me. Could you point me to a source or give me a code example on how to do that.
    The sort of thing you would do is to store a value in a cell when the first work of the week is done, whether that be the first time on Mon, or the first time some later day.

    In your end of week tidy-up, which I presume you have, you would then clear that value, ready for the next week.

  5. #5
    VBAX Expert
    Joined
    Feb 2005
    Posts
    929
    Location
    Quote Originally Posted by xld
    The sort of thing you would do is to store a value in a cell when the first work of the week is done, whether that be the first time on Mon, or the first time some later day.

    In your end of week tidy-up, which I presume you have, you would then clear that value, ready for the next week.
    xld's comments are similar to my thinking. The housekeeping sheet (I typically call it "Housekeeping") is created and a particilar cell is selected for keeping track of when the cleanup is done. Let's say it is cells B2. The last step of the cleanup is to write the date and time to cell B2. The next time the spreadsheet opens, the "check code" examines Worksheets("Housekeeping").Cells(2,2) to see if the cleanup has been "recently" done. If so, cell B2 is left alone. If a new cleanup is appropriate, it is done and cell B2 updated.

    Alternatively, you may wish to keep track of every time the clean-up procedure was run. In that case, you allocate, say, col B for the date. Each time the spreadsheet is opened, the LAST date in col B is checked. If it is time for another cleanup, the cleanup is done and the current date/time is written to the next row in col B.

    Since the housekeeping sheet is pretty important and if corrupted will create problems, it is best to keep it hidden and protected. You can read from a protected sheet, but you can not write to it. So if you needed to write to the sheet, you would unprotect it, write to it and then reprotect it. Something like:
    [VBA]
    Sub CleanUp()

    Dim LastDate As Date
    '
    ' check last date
    '
    LastDate = Worksheets("HouseKeeping").Cells(2, 2)


    '
    ' do cleanup
    '


    '
    ' update HouseKeeping sheet
    '
    Worksheets("HouseKeeping").Unprotect
    Worksheets("HouseKeeping").Cells(2, 2) = Date + Time
    Worksheets("HouseKeeping").Protect

    End Sub

    [/VBA]
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.

  6. #6
    VBAX Expert
    Joined
    Feb 2005
    Location
    Nanaimo, British Columbia, Cananda
    Posts
    568
    Location
    Hi,

    Make a copy of your workbook to test with. Put this code in the ThisWorkbook Module:

    Private Sub Workbook_Open()
    '
    'Checks Date to see if 'Cleanup' needed
    '
    Dim nextrun As Date
    'Gets the Monday from hidden sheet 'DateSheet'
    nextrun = Sheets("DateSheet").Range("a1")
    'Checks if date has past. If not then do nothing
    If Now() < nextrun Then
    	Exit Sub
    End If
    'If date is today or later do your cleanup code - ONCE
     
    '	...your "Cleanup" code goes here...
     
    'Sets next run date as following Monday 's date
    	Sheets("DateSheet").Range("a1") = nextrun + 7
    End Sub
    And this in a standard module:
     
    Sub CreateHiddenSheet()
    '
    'Run this only once
    '
    'Adds a Sheet for "housekeeping?" with the name of 'DateSheet'
    	Sheets.Add.Name = "DateSheet"
    'Gets a Monday date from you to start with
    	ActiveSheet.Range("a1") = InputBox("Enter a Monday date to start with")
    'Hides the sheet from the Format/Unhide menu
    	Sheets("DateSheet").Visible = xlVeryHidden
    End Sub
     
     
    Sub UnhideDateSheet()
    'just in case tou need to see it
    	Sheets("DateSheet").Visible = True
    End Sub
     
    Sub RehideDateSheet()
    	Sheets("DateSheet").Visible = xlveryhidden
    To use it: Run the CreateHiddenSheet macro and give it a Monday's date to start with ie: 6/6/2005 or 13/6/2005. Save and close your workbook. Open it again and you should be in business. The CreateHiddenSheet macro will never be used again....but will give you a hidden sheet in your workbook

    If you need to change the hidden sheet then you'll have to run 'UnhideDateSheet', change it and then run 'RehideDateSheet'.

    If you want, you could protect the hidden sheet too, as long as you include a line that says "Application. userinterfaceonly=true" so the macro can update the protected sheet...


    And here's a bonus!

     
    Sub PostStaticDate()
    '
    'Bonus Sub: How to set a static date:
    '
    	 Range("A1") = DateSerial(Year(Now), Month(Now), Day(Now))
    End Sub
    Thats probably enough for now!

    Cheers,

    dr

  7. #7
    Muchas gracias.


    Those are great ideas, i am sure i can use them

    Last night i also came up with something that works, but certainly it is not as elegant as these examples. When i get of from work i will post my simple solution but for now i really appreciate the input.


Posting Permissions

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