Excel Date Issue

lovinfeelin · #1 (**permalink**) 04-27-10, 06:42

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.

Colin Legg · #2 (**permalink**) 04-27-10, 09:15

Hi,

Quote:

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...

Quote:

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..

lovinfeelin · #3 (**permalink**) 04-28-10, 04:23

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.

Colin Legg · #4 (**permalink**) 04-28-10, 04:47

Hi,

Quote:

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...

lovinfeelin · #5 (**permalink**) 04-28-10, 05:21

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

Internet Services

Web Development

Digital Marketing

Consumer Tech