Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
I would like to create a Task Sheet with Due Dates. The Due Date would be a
certain number of working days from the Order Date. Our work week is four 10-hour days, Monday - Thursday. I wanted to set up a color-code for the Due Date with data validation, but got lost trying to figure out how to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this easily possible with validation? Or would a macro that evaluates each date on open be better? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
How about a user-defined function?
Public Function DueDate(OrderDate As Date, WorkdaysLater As Integer) As Date Dim iCt As Integer Dim iCt2 As Integer Do iCt = iCt + 1 If Weekday(OrderDate + iCt) 1 And _ Weekday(OrderDate + iCt) < 6 Then iCt2 = iCt2 + 1 Loop Until iCt2 = WorkdaysLater DueDate = Format(OrderDate + iCt, "m/d/yy") End Function Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
Ed,
For data validation and conditional formatting, use =IF(AND(WEEKDAY(A1)=2,WEEKDAY(A1)<=5),TRUE,FALSE) This returns True or False indicating whether A1 is between Monday and Thursday. To count the number of dates in A1:A10 that are between Monday and Thursday, use =SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10)< =5)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ed" wrote in message ... I would like to create a Task Sheet with Due Dates. The Due Date would be a certain number of working days from the Order Date. Our work week is four 10-hour days, Monday - Thursday. I wanted to set up a color-code for the Due Date with data validation, but got lost trying to figure out how to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this easily possible with validation? Or would a macro that evaluates each date on open be better? Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
Chip:
Thanks for the help, but I'm not sure I know how to make this work. I put 2/20/2007 in A1, 2/22/2007 in B1, and Conditional Formatting in B1 with Formula Is ="SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10) <=5))<3" should turn the cell yellow. It did not. I tried changing B1 to 2/27/2007 - still no go. What did I miss? Ed "Chip Pearson" wrote in message ... Ed, For data validation and conditional formatting, use =IF(AND(WEEKDAY(A1)=2,WEEKDAY(A1)<=5),TRUE,FALSE) This returns True or False indicating whether A1 is between Monday and Thursday. To count the number of dates in A1:A10 that are between Monday and Thursday, use =SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10)< =5)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ed" wrote in message ... I would like to create a Task Sheet with Due Dates. The Due Date would be a certain number of working days from the Order Date. Our work week is four 10-hour days, Monday - Thursday. I wanted to set up a color-code for the Due Date with data validation, but got lost trying to figure out how to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this easily possible with validation? Or would a macro that evaluates each date on open be better? Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
Merjet:
I've never used a UDF before. I thinking I would name a range OrderDate and put a date in it, then in the Due Date column put the formula =DueDate(OrderDate)"? Did I come close? Ed "merjet" wrote in message ups.com... How about a user-defined function? Public Function DueDate(OrderDate As Date, WorkdaysLater As Integer) As Date Dim iCt As Integer Dim iCt2 As Integer Do iCt = iCt + 1 If Weekday(OrderDate + iCt) 1 And _ Weekday(OrderDate + iCt) < 6 Then iCt2 = iCt2 + 1 Loop Until iCt2 = WorkdaysLater DueDate = Format(OrderDate + iCt, "m/d/yy") End Function Hth, Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
I think you only need the one Weekday formula -
(Weekday(ref-date,3) <=3) ' true if Mon-Thurs It's not clear under what condition(s) you want your CF in B1 to flag. In your example B1 is not in the formula so changing B1 will do nothing. Regards, Peter T "Ed" wrote in message ... Chip: Thanks for the help, but I'm not sure I know how to make this work. I put 2/20/2007 in A1, 2/22/2007 in B1, and Conditional Formatting in B1 with Formula Is ="SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10) <=5))<3" should turn the cell yellow. It did not. I tried changing B1 to 2/27/2007 - still no go. What did I miss? Ed "Chip Pearson" wrote in message ... Ed, For data validation and conditional formatting, use =IF(AND(WEEKDAY(A1)=2,WEEKDAY(A1)<=5),TRUE,FALSE) This returns True or False indicating whether A1 is between Monday and Thursday. To count the number of dates in A1:A10 that are between Monday and Thursday, use =SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10)< =5)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ed" wrote in message ... I would like to create a Task Sheet with Due Dates. The Due Date would be a certain number of working days from the Order Date. Our work week is four 10-hour days, Monday - Thursday. I wanted to set up a color-code for the Due Date with data validation, but got lost trying to figure out how to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this easily possible with validation? Or would a macro that evaluates each date on open be better? Ed |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
I've never used a UDF before. I thinking I would name a range OrderDate and
put a date in it, then in the Due Date column put the formula =DueDate(OrderDate)"? Did I come close? You use it like Excel' worksheet function. That's close, but it has two inputs. Suppose cell A2 holds an order date, and you want a due date 10 days later. In another cell, put the formula: =DueDate(A2, 10) Hth, Merjet |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
I put the Function in the ThisWorkbook module, and I'm getting a #NAME?
error. Ed "merjet" wrote in message oups.com... I've never used a UDF before. I thinking I would name a range OrderDate and put a date in it, then in the Due Date column put the formula =DueDate(OrderDate)"? Did I come close? You use it like Excel' worksheet function. That's close, but it has two inputs. Suppose cell A2 holds an order date, and you want a due date 10 days later. In another cell, put the formula: =DueDate(A2, 10) Hth, Merjet |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
In your example B1 is not in the formula so changing B1 will do nothing.
Thanks, Peter. _Now_ I see that! It's not clear under what condition(s) you want your CF in B1 to flag. I'm trying to get B1 to turn yellow if the date in B1 is less than 3 days from A1, but only counting days Mon-Thurs. So a date of 2/20/2007 in A1 should give me a yellow cell up to 2/26/2007 in B1, and a white cell after that. Ed "Peter T" <peter_t@discussions wrote in message ... I think you only need the one Weekday formula - (Weekday(ref-date,3) <=3) ' true if Mon-Thurs It's not clear under what condition(s) you want your CF in B1 to flag. In your example B1 is not in the formula so changing B1 will do nothing. Regards, Peter T "Ed" wrote in message ... Chip: Thanks for the help, but I'm not sure I know how to make this work. I put 2/20/2007 in A1, 2/22/2007 in B1, and Conditional Formatting in B1 with Formula Is ="SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10) <=5))<3" should turn the cell yellow. It did not. I tried changing B1 to 2/27/2007 - still no go. What did I miss? Ed "Chip Pearson" wrote in message ... Ed, For data validation and conditional formatting, use =IF(AND(WEEKDAY(A1)=2,WEEKDAY(A1)<=5),TRUE,FALSE) This returns True or False indicating whether A1 is between Monday and Thursday. To count the number of dates in A1:A10 that are between Monday and Thursday, use =SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10)< =5)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ed" wrote in message ... I would like to create a Task Sheet with Due Dates. The Due Date would be a certain number of working days from the Order Date. Our work week is four 10-hour days, Monday - Thursday. I wanted to set up a color-code for the Due Date with data validation, but got lost trying to figure out how to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this easily possible with validation? Or would a macro that evaluates each date on open be better? Ed |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
In the VB Editor, use the menu Insert | Module. That adds Module1. Put
the function there. Merjet |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
So isn't that simply
(date2 - date1) < 7 yellow condition or (date2 - date1) 6 white condition where date2 is the later date or am I missing something Regards, Peter T "Ed" wrote in message ... In your example B1 is not in the formula so changing B1 will do nothing. Thanks, Peter. _Now_ I see that! It's not clear under what condition(s) you want your CF in B1 to flag. I'm trying to get B1 to turn yellow if the date in B1 is less than 3 days from A1, but only counting days Mon-Thurs. So a date of 2/20/2007 in A1 should give me a yellow cell up to 2/26/2007 in B1, and a white cell after that. Ed "Peter T" <peter_t@discussions wrote in message ... I think you only need the one Weekday formula - (Weekday(ref-date,3) <=3) ' true if Mon-Thurs It's not clear under what condition(s) you want your CF in B1 to flag. In your example B1 is not in the formula so changing B1 will do nothing. Regards, Peter T "Ed" wrote in message ... Chip: Thanks for the help, but I'm not sure I know how to make this work. I put 2/20/2007 in A1, 2/22/2007 in B1, and Conditional Formatting in B1 with Formula Is ="SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10) <=5))<3" should turn the cell yellow. It did not. I tried changing B1 to 2/27/2007 - still no go. What did I miss? Ed "Chip Pearson" wrote in message ... Ed, For data validation and conditional formatting, use =IF(AND(WEEKDAY(A1)=2,WEEKDAY(A1)<=5),TRUE,FALSE) This returns True or False indicating whether A1 is between Monday and Thursday. To count the number of dates in A1:A10 that are between Monday and Thursday, use =SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10)< =5)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ed" wrote in message ... I would like to create a Task Sheet with Due Dates. The Due Date would be a certain number of working days from the Order Date. Our work week is four 10-hour days, Monday - Thursday. I wanted to set up a color-code for the Due Date with data validation, but got lost trying to figure out how to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this easily possible with validation? Or would a macro that evaluates each date on open be better? Ed |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
Module!! D'oh!!
Works great! Thank you! Ed "merjet" wrote in message oups.com... In the VB Editor, use the menu Insert | Module. That adds Module1. Put the function there. Merjet |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
or am I missing something
I'm only counting Mon-Thurs. If the time range for a particular item is three days, an incoming date of Tues 2/20/07 will have the third day on Thurs 2/22/07, but an incoming date of Wed 2/21/07 will have the third day on Mon 2/26/07. Maybe this is not as hard as I think it is, and I'm missing a simple little thing that will do the trick. Ed "Peter T" <peter_t@discussions wrote in message ... So isn't that simply (date2 - date1) < 7 yellow condition or (date2 - date1) 6 white condition where date2 is the later date or am I missing something Regards, Peter T "Ed" wrote in message ... In your example B1 is not in the formula so changing B1 will do nothing. Thanks, Peter. _Now_ I see that! It's not clear under what condition(s) you want your CF in B1 to flag. I'm trying to get B1 to turn yellow if the date in B1 is less than 3 days from A1, but only counting days Mon-Thurs. So a date of 2/20/2007 in A1 should give me a yellow cell up to 2/26/2007 in B1, and a white cell after that. Ed "Peter T" <peter_t@discussions wrote in message ... I think you only need the one Weekday formula - (Weekday(ref-date,3) <=3) ' true if Mon-Thurs It's not clear under what condition(s) you want your CF in B1 to flag. In your example B1 is not in the formula so changing B1 will do nothing. Regards, Peter T "Ed" wrote in message ... Chip: Thanks for the help, but I'm not sure I know how to make this work. I put 2/20/2007 in A1, 2/22/2007 in B1, and Conditional Formatting in B1 with Formula Is ="SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10) <=5))<3" should turn the cell yellow. It did not. I tried changing B1 to 2/27/2007 - still no go. What did I miss? Ed "Chip Pearson" wrote in message ... Ed, For data validation and conditional formatting, use =IF(AND(WEEKDAY(A1)=2,WEEKDAY(A1)<=5),TRUE,FALSE) This returns True or False indicating whether A1 is between Monday and Thursday. To count the number of dates in A1:A10 that are between Monday and Thursday, use =SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10)< =5)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ed" wrote in message ... I would like to create a Task Sheet with Due Dates. The Due Date would be a certain number of working days from the Order Date. Our work week is four 10-hour days, Monday - Thursday. I wanted to set up a color-code for the Due Date with data validation, but got lost trying to figure out how to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this easily possible with validation? Or would a macro that evaluates each date on open be better? Ed |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
I'm getting confused as to where you count days from.
The date difference between Thurs-Mon in the same week is 3. My original understanding this is within the 0 to 3 difference, Thurs is the 3rd day 'after' Mon, and within the span that triggers your yellow CF. If this the correct interpretation of what you want test my previous suggestion. However below you imply a date difference of 2 is the largest that satisfies the condition (not withstanding Fri-Sun which will need tb excluded). Perhaps give unambiguous pairs of examples with dates in same week and spanning a weekend that just pass and just fail the condition. Regards, Peter T "Ed" wrote in message ... or am I missing something I'm only counting Mon-Thurs. If the time range for a particular item is three days, an incoming date of Tues 2/20/07 will have the third day on Thurs 2/22/07, but an incoming date of Wed 2/21/07 will have the third day on Mon 2/26/07. Maybe this is not as hard as I think it is, and I'm missing a simple little thing that will do the trick. Ed "Peter T" <peter_t@discussions wrote in message ... So isn't that simply (date2 - date1) < 7 yellow condition or (date2 - date1) 6 white condition where date2 is the later date or am I missing something Regards, Peter T "Ed" wrote in message ... In your example B1 is not in the formula so changing B1 will do nothing. Thanks, Peter. _Now_ I see that! It's not clear under what condition(s) you want your CF in B1 to flag. I'm trying to get B1 to turn yellow if the date in B1 is less than 3 days from A1, but only counting days Mon-Thurs. So a date of 2/20/2007 in A1 should give me a yellow cell up to 2/26/2007 in B1, and a white cell after that. Ed "Peter T" <peter_t@discussions wrote in message ... I think you only need the one Weekday formula - (Weekday(ref-date,3) <=3) ' true if Mon-Thurs It's not clear under what condition(s) you want your CF in B1 to flag. In your example B1 is not in the formula so changing B1 will do nothing. Regards, Peter T "Ed" wrote in message ... Chip: Thanks for the help, but I'm not sure I know how to make this work. I put 2/20/2007 in A1, 2/22/2007 in B1, and Conditional Formatting in B1 with Formula Is ="SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10) <=5))<3" should turn the cell yellow. It did not. I tried changing B1 to 2/27/2007 - still no go. What did I miss? Ed "Chip Pearson" wrote in message ... Ed, For data validation and conditional formatting, use =IF(AND(WEEKDAY(A1)=2,WEEKDAY(A1)<=5),TRUE,FALSE) This returns True or False indicating whether A1 is between Monday and Thursday. To count the number of dates in A1:A10 that are between Monday and Thursday, use =SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10)< =5)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ed" wrote in message ... I would like to create a Task Sheet with Due Dates. The Due Date would be a certain number of working days from the Order Date. Our work week is four 10-hour days, Monday - Thursday. I wanted to set up a color-code for the Due Date with data validation, but got lost trying to figure out how to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this easily possible with validation? Or would a macro that evaluates each date on open be better? Ed |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count only Mon-Thurs?
Peter:
Thanks for keeping with me on this, but I think maybe I need to bow out and take a fresher look at this. I've found that when I have to give this much clarification, it's usually because *I* don't understand completely what I'm trying to do, and I run people in circles trying to help me. When I get a better handle on the task, if I still can't get it I'll post back. With great appreciation for all the time! Ed "Peter T" <peter_t@discussions wrote in message ... I'm getting confused as to where you count days from. The date difference between Thurs-Mon in the same week is 3. My original understanding this is within the 0 to 3 difference, Thurs is the 3rd day 'after' Mon, and within the span that triggers your yellow CF. If this the correct interpretation of what you want test my previous suggestion. However below you imply a date difference of 2 is the largest that satisfies the condition (not withstanding Fri-Sun which will need tb excluded). Perhaps give unambiguous pairs of examples with dates in same week and spanning a weekend that just pass and just fail the condition. Regards, Peter T "Ed" wrote in message ... or am I missing something I'm only counting Mon-Thurs. If the time range for a particular item is three days, an incoming date of Tues 2/20/07 will have the third day on Thurs 2/22/07, but an incoming date of Wed 2/21/07 will have the third day on Mon 2/26/07. Maybe this is not as hard as I think it is, and I'm missing a simple little thing that will do the trick. Ed "Peter T" <peter_t@discussions wrote in message ... So isn't that simply (date2 - date1) < 7 yellow condition or (date2 - date1) 6 white condition where date2 is the later date or am I missing something Regards, Peter T "Ed" wrote in message ... In your example B1 is not in the formula so changing B1 will do nothing. Thanks, Peter. _Now_ I see that! It's not clear under what condition(s) you want your CF in B1 to flag. I'm trying to get B1 to turn yellow if the date in B1 is less than 3 days from A1, but only counting days Mon-Thurs. So a date of 2/20/2007 in A1 should give me a yellow cell up to 2/26/2007 in B1, and a white cell after that. Ed "Peter T" <peter_t@discussions wrote in message ... I think you only need the one Weekday formula - (Weekday(ref-date,3) <=3) ' true if Mon-Thurs It's not clear under what condition(s) you want your CF in B1 to flag. In your example B1 is not in the formula so changing B1 will do nothing. Regards, Peter T "Ed" wrote in message ... Chip: Thanks for the help, but I'm not sure I know how to make this work. I put 2/20/2007 in A1, 2/22/2007 in B1, and Conditional Formatting in B1 with Formula Is ="SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10) <=5))<3" should turn the cell yellow. It did not. I tried changing B1 to 2/27/2007 - still no go. What did I miss? Ed "Chip Pearson" wrote in message ... Ed, For data validation and conditional formatting, use =IF(AND(WEEKDAY(A1)=2,WEEKDAY(A1)<=5),TRUE,FALSE) This returns True or False indicating whether A1 is between Monday and Thursday. To count the number of dates in A1:A10 that are between Monday and Thursday, use =SUMPRODUCT((WEEKDAY(A1:A10)=2)*(WEEKDAY(A1:A10)< =5)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Ed" wrote in message ... I would like to create a Task Sheet with Due Dates. The Due Date would be a certain number of working days from the Order Date. Our work week is four 10-hour days, Monday - Thursday. I wanted to set up a color-code for the Due Date with data validation, but got lost trying to figure out how to count only Mon-Thurs, or not count Fri, Sat and Sun?? Is this easily possible with validation? Or would a macro that evaluates each date on open be better? Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Excel Count characters in a textbox to display character count? | Excel Programming | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |