• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

Excel VBA: Date range formula

Hello Experts,

Is there a formula in excel that would provide a weekly range for the previous week (Monday to Sunday).  So example if I was to use the formula now, it will show
10-06-14 to 10-12-14


it should look the same as the above format.

Much appreciated it!
0
Maliki Hassani
Asked:
Maliki Hassani
3 Solutions
 
jkaiosIT DirectorCommented:
To calculate last week:

Dim dtBeg as date, dtEnd as date

dtBeg = DateAdd("ww", -1, Date - Weekday(Date) + 1)
dtEnd = DateAdd("ww", -1, Date - Weekday(Date) + 7)

msgbox dtBeg & " - " & dtEnd

Open in new window

0
 
jkaiosIT DirectorCommented:
After you've calculated last week's date range, you can then format it to the way you want:

msgbox "beg date: " & format(dtBeg, "MM-dd-yy") & vbcrLf
               "end date: " & format(dtEnd, "MM-dd-yy")
0
 
GrahamSkanRetiredCommented:
@Maliki Hassani,
You ask for a formula but you have added VBA Basic programming as a topic. Be aware that VBA and formulae are different things. Formulae will normally give automatic updates in the sheet. VBA macros need to be run specifically and could take considerably more time to process,
0
 
Maliki HassaniAuthor Commented:
Great point. I will use a formula rather than a macro.
0
 
Ejgil HedegaardCommented:
A formula could be like this

=TEXT(TODAY()-WEEKDAY(TODAY(),2)-6,"mm-dd-yy")&" to "&TEXT(TODAY()-WEEKDAY(TODAY(),2),"mm-dd-yy")

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now