Difference between two days

[ibadk]

I'm trying to figure out the number of days between today's day (eg Monday, Tuesday, etc.) and the Sunday of the same week. What I'm trying to do is a balance of the week calculations, meaning that depending on which day of the week I run the report the difference between today's day and sunday will always change. Is there a way for me to do this in VBA?

 

[Dannyh1]

Hi would this work?

Assuming Sunday is last day of week

=8-WEEKDAY(TODAY())

or 1st day of week

=WEEKDAY(TODAY())-1

 

[ibadk]

That does work! How would I now incorporate that into a VBA code? Sorry I'm kind of new to this. I have the following

Sub test4()

Dim diff As Integer





                            diff = 8 - Weekday(today())
                            Range("b30").Activate
                            ActiveCell.Value = diff
End Sub

but when I try and run it the debugger highlights "today" and pops up "compile error: Sub or function not defined"

What am i doing wrong?

Thanks!

 

[MARK858]

Try

Sub test4
Range("B30").Formula = "=8-WEEKDAY(TODAY())"
End Sub