We help IT Professionals succeed at work.

Please modify the query (dates related)

Roman F
Roman F asked
on
226 Views
Last Modified: 2010-04-21
Hi, everybody!
="Total for Week # " & Format$([InspectionDate],"ww",0,0)---> will give me number of the week (32 )
Total for Week# 32

how to make ------>(first day of the week -last day of the week)
Total for Week# 32 (08/04/2008-08/11/2008))
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:

format(DateAdd("d",first day of the week -last day of the week,last day of the week),"ww")

Author

Commented:

sorry,
"the  expression you entered contains invalid syntax
you may have entered a comma without a preceding value or identrifier"

Commented:
="Total For Week # " Format$(InspectionDate,"ww", 0, 0) & "(" & Format(InspectionDate-WeekDay(InspectionDate)+2,"mm/dd/yyyy") & "-" & Format(InspectionDate-WeekDay(InspectionDaet)+9,"mm/dd/yyyy")
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
To GRayL::
same error...

Commented:
I tested my answer in the Immediate pane before posting.  Try it by typing:

InspectionDate = #08/05/2008#

? "Total For Week # " Format$(InspectionDate,"ww", 0, 0) & "(" & Format(InspectionDate-WeekDay(InspectionDate)+2,"mm/dd/yyyy") & "-" & Format(InspectionDate-WeekDay(InspectionDaet)+9,"mm/dd/yyyy") & ")"

This is all one line.

If you need the line breaks, add the line continuation character (underscore followed by Enter.

? "Total For Week # " Format$(InspectionDate,"ww", 0, 0) & "(" & _
Format(InspectionDate-WeekDay(InspectionDate)+2,"mm/dd/yyyy") & "-" & _
Format(InspectionDate-WeekDay(InspectionDaet)+9,"mm/dd/yyyy") & ")"

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, use my formula:

="Total For Week # " & Format$([InspectionDate],"ww", 0, 0) & " (" & Format(DateWeekFirst([InspectionDate], 2), "mm/dd/yyyy") & "-" & Format(DateWeekFirst(DateAdd("ww", 1, [InspectionDate]), 2), "mm/dd/yyyy") & ")"

It will even return the correct end date.

/gustav

Author

Commented:
thank You, great
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.