Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count by monday, tuesday, ...in a year?
I'm curently working on the spreadsheet to count how many reservations
made by Monday, Tuesday, Wednsday, etc. Yes, I work at hospitablity business, we want to know the production(reservation) made by each day so we can better arrange the staffs. Since, we are looking at resevation date not the check in date, so those reservations weren't necessarily made in the same month. some of them can be back to 6 months ago. I don't know which function in excel could generate data from the format of " Sunday, Febuary 21, 2006" and sum the day by "Monday, tuesday, .." I hope my question is clear. Thank you for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count by monday, tuesday, ...in a year?
where col A is properly formatted dates and e1 has the date desired. Weekday
2 is Monday =SUMPRODUCT((A3:A33E1)*(WEEKDAY(A3:A33)=2)) -- Don Guillett SalesAid Software wrote in message ps.com... I'm curently working on the spreadsheet to count how many reservations made by Monday, Tuesday, Wednsday, etc. Yes, I work at hospitablity business, we want to know the production(reservation) made by each day so we can better arrange the staffs. Since, we are looking at resevation date not the check in date, so those reservations weren't necessarily made in the same month. some of them can be back to 6 months ago. I don't know which function in excel could generate data from the format of " Sunday, Febuary 21, 2006" and sum the day by "Monday, tuesday, .." I hope my question is clear. Thank you for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count by monday, tuesday, ...in a year?
Thank you very much Richard. Your method works great! Just wonder how
you did that, where I can find more information if I want to learn more about things like this? Thanks again. Julia RichardSchollar wrote: You could use: =SUMPRODUCT(--(TEXT(A1:A1000,"dddd")="Monday")) to count how many Mondays there are in cells A1:A1000. The criteria can be a cell reference (ie a cell containing Monday, or Tuesday, etc...). Adjust your range to suit. Hope this helps! Richard |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count by monday, tuesday, ...in a year?
Don,
I'm not quite sure what e1 means, but thank you anyway for looking at my question. My problem is solved by using Richard's method above. Thanks again. The group is so wonderful because having people like you. Have a good holiday! Julia Don Guillett wrote: where col A is properly formatted dates and e1 has the date desired. Weekday 2 is Monday =SUMPRODUCT((A3:A33E1)*(WEEKDAY(A3:A33)=2)) -- Don Guillett SalesAid Software |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count by monday, tuesday, ...in a year?
Hey Julia
I have literally learned about formulas by reading and answering (when I can!) message board posts (of course, helps if you have a *need* for the formula too eg at work). My personal favourite message board (Excel related) is www.MrExcel.com/board2 Drop by and say Hi! - I lurk their under the same handle. Best regards & have a wonderful Christmas/New Year! Richard wrote: Thank you very much Richard. Your method works great! Just wonder how you did that, where I can find more information if I want to learn more about things like this? Thanks again. Julia RichardSchollar wrote: You could use: =SUMPRODUCT(--(TEXT(A1:A1000,"dddd")="Monday")) to count how many Mondays there are in cells A1:A1000. The criteria can be a cell reference (ie a cell containing Monday, or Tuesday, etc...). Adjust your range to suit. Hope this helps! Richard |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count by monday, tuesday, ...in a year?
Your request said
" data from the format of " Sunday, Febuary 21, 2006" if cell e1 had a properly formatted date -- Don Guillett SalesAid Software wrote in message oups.com... Don, I'm not quite sure what e1 means, but thank you anyway for looking at my question. My problem is solved by using Richard's method above. Thanks again. The group is so wonderful because having people like you. Have a good holiday! Julia Don Guillett wrote: where col A is properly formatted dates and e1 has the date desired. Weekday 2 is Monday =SUMPRODUCT((A3:A33E1)*(WEEKDAY(A3:A33)=2)) -- Don Guillett SalesAid Software |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count by monday, tuesday, ...in a year?
There is a detailed explanation at
http://xldynamic.com/source/xld.SUMPRODUCT.html -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message ups.com... Thank you very much Richard. Your method works great! Just wonder how you did that, where I can find more information if I want to learn more about things like this? Thanks again. Julia RichardSchollar wrote: You could use: =SUMPRODUCT(--(TEXT(A1:A1000,"dddd")="Monday")) to count how many Mondays there are in cells A1:A1000. The criteria can be a cell reference (ie a cell containing Monday, or Tuesday, etc...). Adjust your range to suit. Hope this helps! Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to count spinoffs | Excel Worksheet Functions | |||
Subtotals by count | Excel Discussion (Misc queries) | |||
Count consecutive dates only | Excel Discussion (Misc queries) | |||
Finding the date on the 'nth' Monday in this Month in this Year | Excel Worksheet Functions | |||
count no. of dates in a column that falls on certain month & year | Excel Worksheet Functions |