Excel – formula for exel to calculate how many days from speefic date untill today

Here's the equation:

=IF(WEEKDAY(B2)=B1, INT((DAYS(B3,B2)/B4)-1), INT(DAYS(B3,B2)/B4))

Description:

• The day of the week you get paid needs to be put in cell B1 encoded as Sunday=1, Monday=2, .. ,Saturday=7.

• The cell B2 should have the starting date.

• The cell B3 should have the end date.

• How often you get paid in days needs to go cell B4 (i.e. once per week=7,every other week=14).

• This will not count the current payday if the start day is a payday. To remove that, just use =INT(DAYS(B3,B2)/B4)).

I would like to suggest TWO Solutions, to achieve the Goal.

Solution 1:

Use these VBA codes as Module. First will convert the Current Date in cell A1 into a Static Date Value while Saving the Workbook and Second Macro will Convert the Static Date Value into Volatile.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    With Sheet1.Range("A1")
        .Value = .Value
    End With

End Sub


Private Sub Workbook_Open()

     With Sheet1.Range("A1")
        .Value = "=Today()"
    End With

    End Sub

Solution 2:

In place of two different Macros you can create a Toggle Button to Convert the Volatile Date into Static and again to Volatile Date value.

    Private Sub CommandButton1_Click()

     If CommandButton1.Caption = "Convert Static" Then

      With Sheet1.Range("A1")
      .Value = .Value
      End With

   CommandButton1.Caption = "Convert Volatile"

   ElseIf CommandButton1.Caption = "Convert Volatile" Then

    With Sheet1.Range("A1")
            .Value = "=Today()"
        End With

  CommandButton1.Caption = "Convert Static"
  End If

  End Sub

NB: Follow these steps to accomplish the Job.

1. In the sheet 1 where you execute your Formula, in Cell A1 write this Formula = Today().
2. For the solution 1, Press Alt+F11 to open the VBA Editor, find the "Microsoft Excel Objects".
3. Right Click the Mouse on it, find Insert then Module.
4. Copy & Paste both the Macros.

For Solution 2, follow these steps.

1. In Sheet 1, Click the Developer Tab, find Design Mode, left to it is Insert.
2. Find ActiveX Controls, the first is Command Button, Click it and draw the button in blank area.
3. Right Click the Command button, Find Properties, Find Caption and change it to Convert Static.
4. Return to Sheet, double click the Command button, VBA editor will be open.
5. Copy and Paste the VBA Code, go to File menu, hit Close and Return to Excel.
6. Again click the Design button on top to turn it deactivate.
7. Click the Command Button, will change Volatile Date value in Cell A1 to Static, and change the Caption of Command Button also to, Convert Volatile.
8. Click it again when you want to change Cell value A1 to Static.

Remember, on your each Click the Macro will convert Date in A1 cell Volatile to Static to Volatile.

Note, in YOUR FORMULA use Cell A1 instead of TODAY().

You are free to write =Today() Formula in any BLANK CELL but don't forget to modify the Cell Address accordingly.

I'm sure this help you.