I have four cells.

Cell A1 that contains a date the user enters manually.

Cell A2 needs to contain the date for the previous work day from the date entered into cell A1.

Cell A3 needs to contain the number of work days that have passed from the first work day of the month thru the date in cell A2.

Cell A4 needs to contain the total number of work days in a specific month.

Our work days are Monday thru Saturday.

So for the month of March, cell A4 would have the number 27.

If 3/5/07 is entered in cell A1, cell A2 would have 3/3/07, and cell A3 would have the number 3.

If 3/8/07 is entered in cell A1, cell A2 would have 3/7/07, and cell A3 would have the number 6.

Do I need another cell that contains the month and year so the formula in cell A4 knows which month to count the working days for or could it reference the date in cell A2 to know which month and year to use?

Thanks in advance for your time.

A2: DateAdd function (this can only be used in VBA code)
Syntax: DateAdd ( interval, number, date )
Example: DateAdd (d, -1, <YourCell>)

A3: DateDiff function (also only available in VBA)
Syntax: DateDiff( interval, date1, date2 )

A4: Again - DateDiff

HTH

I can't think of a way to do this without VB, either. Take a look here
DBForums Code Bank
I posted some code in there that does just what your looking for.
You'll just have to make some changes for a six day week, and adapt it
to put the data into your cells.

You might want to look at all the date functions available in Excel and the those from the Analysis ToolPak.

I came across the formula below. Does anyone see any reason why this might cause me problems?

What I'm hoping this formula does is: count number of work days for a 6 day work week Monday thru Saturday. Cell A1 contains the first day of the month, cell A2 contains the last work date.

=NETWORKDAYS(A1,A2)+(INT((((A2+6)-(A1+6))+MOD(7-(A1+6),7))/7)+1)

So if cell A1 contains 3/1/07 and cell A2 contains 3/17/07, the formula would return the number 15

Thanks,
Zenaida
________________________
Using Excel 2000

I tested the above formula with different dates and it didn't work. Couldn't figure out what the formula was doing. It would give me different numbers with the same end date??

Anyway, I've been testing the formula below. If anyone thinks I might have a problem with it, I would greatly appreciate any comments.

=NETWORKDAYS(A1,A2)+INT(A2/7)-INT(A1/7)+INT(WEEKDAY(A1)/7)

_________________
Using Excel 2000


Thanks,
Zenaida

I suggest you start by splitting the formula so you can work out what each bit is doing.

=NETWORKDAYS(A1,A2)
=INT(A2/7)-INT(A1/7)
=INT(WEEKDAY(A1)/7)