Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

Is there a VBA code or formula that will change the value of a cell to the Monday date…

  1. User

    Is there a VBA code or formula that will change the value of a cell to the Monday date of the week

  2. Assistant

    Yes, you can use VBA code or formulas to change the value of a cell to the Monday date of the week. Here are some examples:

    1. VBA code: You can use the following VBA code to set the value of a cell to the Monday date of the current week:
    Sub SetCellToMondayDate()
        Dim dt As Date
        dt = Date - Weekday(Date, vbMonday) + 1 ' Monday date
        Range("A1").Value = dt ' Change A1 to the desired cell
    End Sub
    

    To use this code, press ALT + F11 to open the VBA editor, insert a new module, and paste the code into the module. Run the macro by pressing F5 or manually executing it from the Macro menu.

    1. Formula: You can use the following formula to calculate the Monday date of the current week:
    = TODAY() - WEEKDAY(TODAY(), 2) + 1
    

    This formula uses the TODAY() function to get the current date, subtracts the number of days since Monday (using WEEKDAY(TODAY(), 2)), and adds 1 to get the Monday date of the week.

    You can enter this formula directly into the desired cell or use it within another formula for further calculations.

    Note that the formulas will update automatically whenever the workbook is opened or the worksheet is recalculated, whereas VBA code needs to be manually executed.