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.

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.

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

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