| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date formula not picking up 1st of the month (weekday)
Hi, we have a very complex sales forecast spreadsheet whereby we need to
calculate the number of orders and quotes (between two dates) from the information entered by the sales people, they enter the quote or order date and the formula calculates based upon this date. We find this works for all other dates in the month except it will not count the 1st! Any ideas guys, we don't want to be fudging the dates. Formula used: =COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<=39202")-COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<=39173") (This is for APRIL). Office/Excel Ver. 2003. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date formula not picking up 1st of the month (weekday)
You are subtracting the number of days that are less than OR EQUAL TO April 1
Change the second COUNTIF from "<=" to "<" "Ang" wrote: Hi, we have a very complex sales forecast spreadsheet whereby we need to calculate the number of orders and quotes (between two dates) from the information entered by the sales people, they enter the quote or order date and the formula calculates based upon this date. We find this works for all other dates in the month except it will not count the 1st! Any ideas guys, we don't want to be fudging the dates. Formula used: =COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<=39202")-COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<=39173") (This is for APRIL). Office/Excel Ver. 2003. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date formula not picking up 1st of the month (weekday)
Maybe...
=COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<=39202") -COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<39173") (dropped the = sign on the second =countif().) Personally, I find this easier to read: =COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<="&date(2007,4,30)) -COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<"&date(2007,4,1)) Ang wrote: Hi, we have a very complex sales forecast spreadsheet whereby we need to calculate the number of orders and quotes (between two dates) from the information entered by the sales people, they enter the quote or order date and the formula calculates based upon this date. We find this works for all other dates in the month except it will not count the 1st! Any ideas guys, we don't want to be fudging the dates. Formula used: =COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<=39202")-COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<=39173") (This is for APRIL). Office/Excel Ver. 2003. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date formula not picking up 1st of the month (weekday)
Thanks guys, i will give this a go.
I knew it would be simple! "Dave Peterson" wrote: Maybe... =COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<=39202") -COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<39173") (dropped the = sign on the second =countif().) Personally, I find this easier to read: =COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<="&date(2007,4,30)) -COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<"&date(2007,4,1)) Ang wrote: Hi, we have a very complex sales forecast spreadsheet whereby we need to calculate the number of orders and quotes (between two dates) from the information entered by the sales people, they enter the quote or order date and the formula calculates based upon this date. We find this works for all other dates in the month except it will not count the 1st! Any ideas guys, we don't want to be fudging the dates. Formula used: =COUNTIF('Live Forecast'!$AK$6:$AK$6882,"<=39202")-COUNTIF('Live Forecast'!$AK$6:$AK$6886,"<=39173") (This is for APRIL). Office/Excel Ver. 2003. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| picking out date ranges | Excel Worksheet Functions | |||
| convert weekday date to the next sunday date | Excel Worksheet Functions | |||
| WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
| find date of last weekday of a month | Excel Discussion (Misc queries) | |||
| Weekday formula calculating to end of month | Excel Worksheet Functions | |||
Linear Mode
