Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37

    Unanswered: Excel Date Issue

    Morning all,

    What i require is, that a date field updates based on the current month on the press of a button, but the date must always be the first of that current month.

    I'll try to explain more clearly. I have a Sheet which we use to log checks and across the top of the page there are the dates for each day of the month in the format 01/01/2010 for example. What I need is for the date 01/01/2010 to update to 01/02/2010 when the date has changed from January to February. If possible i would like it done in a macro that I already have attached to clear out old entries in the previous month.

    I also have a question about Excel and recognising days of the week. How intelligent is Excel with regards to this. Is excel able to determine from the date of the month what day of the week it is and if so, could it then automatically update information in a cell (such as colouring a cell in if it was a saturday).

    Thanks for taking the time to read.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    What I need is for the date 01/01/2010 to update to 01/02/2010 when the date has changed from January to February. If possible i would like it done in a macro that I already have attached to clear out old entries in the previous month.
    To increase the month by one you can use a procedure such as this:
    Code:
    Sub foo()
        Dim rngDates As Range
        Dim strFormula As String
        Set rngDates = Sheet1.Range("C2:C31")
     
        strFormula = "=IF(ISNUMBER(" & rngDates.Address & "),DATE(YEAR(" & _
                        rngDates.Address & "),MONTH(" & rngDates.Address & ")+1,1),"""")"
     
        rngDates = rngDates.Parent.Evaluate(strFormula)
     
     
    End Sub
    Just set rngDates to the range you need. It's up to you to choose the event that will trigger this code - clicking a button, opening the workbook, etc...

    I also have a question about Excel and recognising days of the week. How intelligent is Excel with regards to this. Is excel able to determine from the date of the month what day of the week it is and if so, could it then automatically update information in a cell (such as colouring a cell in if it was a saturday).
    Yes, Excel has extensive worksheet functions for working with dates. There are also numerous functions in the VBA library.

    In terms of worksheet functions, a formula such as this will visually tell you the day of the week:
    =TEXT(A1,"ddd")
    Where A1 holds a date.


    Other functions that may be of interest are DAY() and WEEKDAY().

    To highlight a cell you could use conditional formatting. The DAY() function returns 6 if it is a Saturday, so the conditional formatting formula in cell A1 would be something like this:
    =DAY($A$1)=6


    Hope that helps..
    Last edited by Colin Legg; 04-27-10 at 11:21.

  3. #3
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    Hello Colin,

    Thank you for your help, that first part of code I have added and modified and it does indeed add One month to the date, however it doesnt quite do what I wanted.

    What I need the sheet to do is set to the 1st date of the current month. So for example, we have the check sheet for April, which we fill in for the whole month, I then copy and paste the sheet to create a new one, then have to spend about half an hour, updating the sheet for the next month, clearing out old cells, changing the date and and moving weekends around and I was trying to automate the process. If there is no another way than this is a good solution that i'm sure my colleagues can work with.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    I have added and modified and it does indeed add One month to the date, however it doesnt quite do what I wanted.

    What I need the sheet to do is set to the 1st date of the current month.
    The good news is that is more straightforward:
    Code:
    Sub foo()
        Dim rngDates As Range
        Const strFORMULA As String = "=DATE(YEAR(TODAY()),MONTH(TODAY()),1)"
        
        Set rngDates = Sheet1.Range("C2:C31")
        rngDates.Formula = strFORMULA
        rngDates.Value = rngDates.Value
        
    End Sub
    This could all be done with formulas in the worksheet (no VBA required).

    Hope that helps...

  5. #5
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    Hello Colin,

    Thank you again for your help, this does exactly what I need now. I have also managed to get my other issue with the days sorted too, thanks again for your help with that one.

    LF

Posting Permissions

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