Sum Qty based on two date range

[mailme_jaguar]

Hi Experts,

I have dates in the format dd-mmm-yyyy in Column "E" and Qty (Nos) in Column "F". In user form, I have 2 text boxes ("Start" and "End" Date) and on inputting dates in text boxes,I need the sum of qty to be calculated with respect to all date range in "E" and the Sum Qty in a msgbox.

[danielexcelvba]

Could you attach a sample workbook with desensitized data? I'm wondering if
1) all your dates are in that format
2) if they have to be in that format instead of an excel-recognized date format?

[danielexcelvba]

If they are all in that format you could try something like this (I used input boxes, you will have to edit that to use your userform)

Please Login or Register  to view this content.

Edit: you're also going to have to change the loop starting values to "2" if you have titles in columns "E" and "F" instead of the data starting at row 1

[Arkadi]

This ought to work... but convert the textboxes to actual dates to be safe... dates in excel can get messy:

Please Login or Register  to view this content.

[danielexcelvba]

Well that's 1000x easier. Didn't know you could recognize dates in that format.

[Arkadi]

@daniel, yes, you should be able to.... I am assuming the cells are formatted as a date in that format? I always try to work with date variables rather than strings but if i HAVE to have a string, i paste it in the dd-mmm-yyyy format so that excel can't mistake the month for the day and vice-versa.

[danielexcelvba]

I read it as dd-mm-yyyy (which is not a recognized format)
Even then your code would work though!

[Arkadi]

True... As long as cells are formatted as a date and the date variable in VBA is set correctly, the code should be fine.
CDate could still get confused with a textbox though, if the textbox has a dd-mm-yy entry or mm-dd-yy (all depending on the system date settings).
I always use a custom calendar form I have, that allows me to force the user to pick a date, and that will update a textbox with the dd-mmm-yyyy format. Or give the user specific day month and year textboxes and use dateserial.

[danielexcelvba]

Yeah I just figured it was formatted as text since dd-mm-yyyy would get screwed up if it was set to date.

[Arkadi]

daniel, in cell formatting if you pick custom format, you can specify dd-mm-yyyy... and that would still be formatted as an actual date. You are correct, it is not an option in the "Date" settings, but will still be seen as a date by Excel.