Calculating monthly start date with a start date

[Monique]

I have a spreadsheet that I use to calculate dates for inventory supplies. I
have a formula that works great except for one function - I would like to be
able to use the start date of any month (January, February, March etc.) in
calculating what the next working day will be after entering 1/1/08, 2/1/08
etc.

The formula I now use is =WORKDAY(C4,1,holidays)

This formula does not choose the first working day correctly. January is
correct when I insert 1/1/08, February also, with 2/1/08 but March 3/1/08 is
on a Saturday. I would like my formula to also calculate the next start date
of the month, even if I use 1/1/08 or 2/1/08 or 3/1/08 as a start date.



i.e. C4: 3/1/08

=WORKDAY(U4,1,holidays)
=WORKDAY(W4,1,holidays)
copied to:
C 40 - S 40

The month ends on the proper day.

I am not sure this is possible, but I would love someone who could help me
figure out how to do it.

Thanks!

Monique

 

[Ron Rosenfeld]

I have a spreadsheet that I use to calculate dates for inventory supplies. I
have a formula that works great except for one function - I would like to be
able to use the start date of any month (January, February, March etc.) in
calculating what the next working day will be after entering 1/1/08, 2/1/08
etc.

The formula I now use is =WORKDAY(C4,1,holidays)

This formula does not choose the first working day correctly. January is
correct when I insert 1/1/08, February also, with 2/1/08 but March 3/1/08 is
on a Saturday. I would like my formula to also calculate the next start date
of the month, even if I use 1/1/08 or 2/1/08 or 3/1/08 as a start date.



i.e. C4: 3/1/08

=WORKDAY(U4,1,holidays)
=WORKDAY(W4,1,holidays)
copied to:
C 40 - S 40

The month ends on the proper day.

I am not sure this is possible, but I would love someone who could help me
figure out how to do it.

Thanks!

Monique

Perhaps I am not understanding your problem correctly, as I don't see how
February would be correct using 2/1/08 in C4.

What I understand is that, with some random date in C4, you want your formula
to return the first working day of the month in C4.

If that is correct, then another way of stating that is that you want to return
the first working day after the last day of the month preceding the date in C4.

To do that:

=WORKDAY(C4-DAY(C4),1,holidays)
--ron

 

[Monique]

Hi Ron,

I couldn't get this to work in the C4 cell like I wanted to but I left it in
C4 and put the date I wanted in B4. I changed the font color to white so the
date in B4 doesn't print, and now I do have what I wanted. Thank you, that
was helpful!

Monique

 

[Ron Rosenfeld]

Hi Ron,

I couldn't get this to work in the C4 cell like I wanted to but I left it in
C4 and put the date I wanted in B4. I changed the font color to white so the
date in B4 doesn't print, and now I do have what I wanted. Thank you, that
was helpful!

Monique

Well, the cell that you reference cannot be in the same cell that you have the
formula. Glad you got it working.
--ron