Office 365 - EXCEL VBA SUMIFS with Variable Date
I can sum spreadsheet values based on the dates entering them manually like this. Dates are in column B and dollar values in column N.
I have issues trying it like with date variables: week7_start, week7_end. I don't get anything summed doing it this way.
I have tried using Clng & CDate but it doesn't seem to matter. What am I missing?
Here's what the output looks like on my spreadheet.
SUMIFS(N4:N144,B4:B144,">=2015-02-09",B4:B144,"<=2015-02-14")I have issues trying it like with date variables: week7_start, week7_end. I don't get anything summed doing it this way.
SUMIFS(N4:N144,B4:B144,">="&week7_start,B4:B144,"<="&week7_end)I have tried using Clng & CDate but it doesn't seem to matter. What am I missing?
SUMIFS(N4:N144,B4:B144,">="&Clng(week7_start),B4:B144,"<="&Clng(week7_end))Here's what the output looks like on my spreadheet.
Week Start Date End Date
Week 7 $(93.31) 8-Feb 14-Feb Manual dates
Week 7 $- 8-Feb 14-Feb Variable dates
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am defining variables on a separate worksheet and giving them names to make them easily recognizable in my formulas. The variables week7_start week7_end are formatted as dates like: 8-Feb 14-Feb. Perhaps the issue is not displaying the year.
The idea is to update my variables once a quarter and then all my formulas will follow suit. There will be 14 weeks when I am done.
It's really not VBA at this point. Sorry I misled you.
The idea is to update my variables once a quarter and then all my formulas will follow suit. There will be 14 weeks when I am done.
It's really not VBA at this point. Sorry I misled you.
ASKER
I notice that when I format the dates as month/day/year rather than just day/month I end up with 2014 rather than the expected 2015. I have re-used an old spreadsheet so maybe that's the issue.
ASKER
Now the formulas work. They were apparently trying to compare last year's date. Thanks for your suggestions to get me started in the right direction.
SUMIFS(N4:N144,B4:B144,">="&Clng(week7_start),B4:B144,"<="&Clng(week7_end))
I don't recognise those as standard Excel functions.
Do you have a User Defined Function to generate the dates?
Thanks
Rob H