Solved

Excel VBA:  Date range formula

Posted on 2014-10-15
5
326 Views
Last Modified: 2014-10-15
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
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 12

Assisted Solution

by:jkaios
jkaios earned 167 total points
ID: 40383018
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
 
LVL 12

Expert Comment

by:jkaios
ID: 40383025
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
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 166 total points
ID: 40383057
@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
 

Author Comment

by:Maliki Hassani
ID: 40383078
Great point. I will use a formula rather than a macro.
0
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 167 total points
ID: 40383091
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question