Need a DAYSINMONTH function to create a sales pace spreadsheet

  • Thread starter Thread starter MOTOGEEK
  • Start date Start date
M

MOTOGEEK

I'm trying to use the date function to set some
parameters to compute a sales pace function.

For example, today is April 10th. I have sold 10 units so
far this month. Therefor my sales pace is 10units/10days
X the number of days in the month (30) - Holidays
(easter=-1). The simple answer is 29 (one a day)

The challenges are as follows:

1. Date is a volatile function. How can I have the date
stay the same so the computations aren't refigured each
time the file is re-opened?

2. The date then needs to return a number equal to the
number of the day, so that it can be subtracted from the
number of days in the month.

3. Is there a function that will compute the number of
days in that particular month, based on the date, perhaps
allowing for a substraction of Holidays? (Being a retail
location, we are open every day except major holidays,
such as Christmas, Easter, and Thanksgiving (US))


OpenOffice.org's Calc speadsheet has a "DAYSINMONTH"
function, that can compute the 2nd question, but is has a
volatile date function.

I don't mind putting the date in, (in fact, it would
allow the worksheet to be computed after the fact,
without changing the computer's date), but I can't seem
to find a days of month function in Excel.

Any ideas?


Pat
 
Hi
1. For creating a fixed date have a look at
http://www.mcgimpsey.com/excel/timestamp.html

2. If you put this fixed date in A1 use
=DAY(A1)
to get the day of the month

3. Dates of the month:
- without holidays:
=DATE(YEAR(A1),MONTH(A1)+1,1)-DATE(YEAR(A1),MONTH(A1),1)

- with holidays and only counting weekdays:
=NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)+1,1),li
st_of_holidays)
 
I'm trying to use the date function to set some
parameters to compute a sales pace function.

For example, today is April 10th. I have sold 10 units so
far this month. Therefor my sales pace is 10units/10days
X the number of days in the month (30) - Holidays
(easter=-1). The simple answer is 29 (one a day)

The challenges are as follows:

1. Date is a volatile function. How can I have the date
stay the same so the computations aren't refigured each
time the file is re-opened?
Click to expand...

The date will have to be entered manually, or via a shortcut key such as
2. The date then needs to return a number equal to the
number of the day, so that it can be subtracted from the
number of days in the month.
=DAY(A1)


3. Is there a function that will compute the number of
days in that particular month, based on the date, perhaps
allowing for a substraction of Holidays? (Being a retail
location, we are open every day except major holidays,
such as Christmas, Easter, and Thanksgiving (US))
Click to expand...

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))-(MONTH(A1)>=11) will account for Christmas
and Thanksgiving. But since the computation for Easter is not trivial, and
could be in either March or April, it would be best to store a list of Easter
Dates in a range someplace, and then check that range for a corresponding
month/year.
OpenOffice.org's Calc speadsheet has a "DAYSINMONTH"
function, that can compute the 2nd question, but is has a
volatile date function.

I don't mind putting the date in, (in fact, it would
allow the worksheet to be computed after the fact,
without changing the computer's date), but I can't seem
to find a days of month function in Excel.

Any ideas?


Pat
Click to expand...

--ron
 
...
...
3. Dates of the month:
- without holidays:
=DATE(YEAR(A1),MONTH(A1)+1,1)-DATE(YEAR(A1),MONTH(A1),1)
Click to expand...

or, originally from Daniel Maher,

=32-DAY(A1-DAY(A1)+32)
- with holidays and only counting weekdays:
=NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)+1,1),
list_of_holidays)
Click to expand...

applying the expression above,

=NETWORKDAYS(A1-DAY(A1)+1,A1-DAY(A1)+32-DAY(A1-DAY(A1)+32),list_of_holidays)
 
Back
Top