function to fill all days of month to end of month

[Monique]

I would like to create a monthly inventory, based on workdays (Monday -
Friday)Myrna Larson has a formula that I would like to use with the workday
function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))
+ =workday

to fit on the page, I need the dates to be from the 1st to the 15th, and
16th to the 31st. I am not sure how to write this either.

Thank you for your response.

Monique

[SteveG]

Monique,

Assuming that your start date is in A1 (entered manually) and your range is
A1:L1 for 1-15 then in B1 enter:

=IF(A1="","",IF(DAY(WORKDAY(A1,1))>15,"",WORKDAY(A1,1)))

Copy this over to L1

Not sure where you are putting your 16 - 31 but for this example say it is
A6:L6

In A6:

=WORKDAY(MAX(A1:L1),1)

In B6

=IF(A6="","",IF(DAY(WORKDAY(A6,1))>31,"",IF(MONTH(A6+1)>MONTH(A1),"",WORKDAY(A6,1))))

Copy this over to L6.

Does that help?

Steve

[Monique]

Thanks Steve! It worked like a charm!

"Monique" wrote:

> I would like to create a monthly inventory, based on workdays (Monday -
> Friday)Myrna Larson has a formula that I would like to use with the workday
> function, but I don't know how to combine them.
> =IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))
> + =workday
>
> to fit on the page, I need the dates to be from the 1st to the 15th, and
> 16th to the 31st. I am not sure how to write this either.
>
> Thank you for your response.
>
> Monique
>

[Monique]

I checked a few of the months, and December and September are adding one or
two day of the next month. I am not sure why.

"SteveG" wrote:

>
> Monique,
>
> Assuming that your start date is in A1 (entered manually) and your
> range is
> A1:L1 for 1-15 then in B1 enter:
>
> =IF(A1="","",IF(DAY(WORKDAY(A1,1))>15,"",WORKDAY(A1,1)))
>
> Copy this over to L1
>
> Not sure where you are putting your 16 - 31 but for this example say it
> is
> A6:L6
>
> In A6:
>
> =WORKDAY(MAX(A1:L1),1)
>
> In B6
>
> =IF(A6="","",IF(DAY(WORKDAY(A6,1))>31,"",IF(MONTH(A6+1)>MONTH(A1),"",WORKDAY(A6,1))))
>
> Copy this over to L6.
>
> Does that help?
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=537695
>
>

[SteveG]

Monique,

This should do it for you. I checked this for each month of 2006.

=IF(A6="","",IF(DAY(WORKDAY(A6,1))>31,"",IF(MONTH(WORKDAY(A6,1))>MONTH($A$1),"",IF(YEAR(WORKDAY(A6,1))>YEAR(A1),"",WORKDAY(A6,1)))))

Copy accross to L6.

Regards,

Steve

[Monique]

I played around with the cell on the last day and changed the formula in K6.
It seems to work the way I intended it to. Thanks for helping me figure out
the hard part!


=IF(K6="","",IF(DAY(WORKDAY(K6,1))>31,"",IF(MONTH(K6+1)>MONTH(L1),"",WORKDAY(K6,1))))


"Monique" wrote:

> Thanks Steve! It worked like a charm!
>
> "Monique" wrote:
>
> > I would like to create a monthly inventory, based on workdays (Monday -
> > Friday)Myrna Larson has a formula that I would like to use with the workday
> > function, but I don't know how to combine them.
> > =IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))
> > + =workday
> >
> > to fit on the page, I need the dates to be from the 1st to the 15th, and
> > 16th to the 31st. I am not sure how to write this either.
> >
> > Thank you for your response.
> >
> > Monique
> >

[SteveG]

Slight typo. You need to change the A1 in the YEAR formula to an absolute reference.

=IF(A6="","",IF(DAY(WORKDAY(A6,1))>31,"",IF(MONTH( WORKDAY(A6,1))>MONTH($A$1),"",IF(YEAR(WORKDAY(A6,1 ))>YEAR($A$1),"",WORKDAY(A6,1)))))

[Biff]

Can you explain the need for:

IF(DAY(WORKDAY(A6,1))>31,"",

That expression will never be TRUE.

Biff

"SteveG" <[email protected]> wrote in
message news:[email protected]...
>
> Slight typo. You need to change the A1 in the YEAR formula to an
> absolute reference.
>
> =IF(A6="","",IF(DAY(WORKDAY(A6,1))>31,"",IF(MONTH(
> WORKDAY(A6,1))>MONTH($A$1),"",IF(YEAR(WORKDAY(A6,1
> ))>YEAR($A$1),"",WORKDAY(A6,1)))))
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile:
> http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=537695
>

[daddylonglegs]

Slight typo. You need to change the A1 in the YEAR formula to an absolute reference.

=IF(A6="","",IF(DAY(WORKDAY(A6,1))>31,"",IF(MONTH( WORKDAY(A6,1))>MONTH($A$1),"",IF(YEAR(WORKDAY(A6,1 ))>YEAR($A$1),"",WORKDAY(A6,1)))))

Hi Steve/Monique

checking IF(DAY(WORKDAY(A6,1))>31 is redundant since it can never be true. Won't this formula in B6 copied across be sufficient?

=IF(A6="","",IF(MONTH(WORKDAY(A6,1))<>MONTH(A6),"",WORKDAY(A6,1)))

[SteveG]

Biff,

Good point. I was following my logic not to exceed the 15th of the month by using [ =IF(DAY(WORKDAY(A1,1))>15,"", ]. Of course it is not needed unless someone decides to change our date systems and make 32 day months. Thanks for catching that.



Steve

[SteveG]

daddylonglegs/Biff,

Yes. Thanks for streamlining my thought processes. I used the YEAR in the formula to account for the end of year since the month would then be 1 which is not less than 12 and populated the first few days of January 2007. Using [MONTH(WORKDAY(A6,1))<>MONTH(A6),""] effectively does the same as well as stops the day at the last workday for all months.



Steve

[Monique]

Hi Steve,

I did copy and paste this part. It shows up as:

=IF(A6="","",IF(DAY(WORKDAY(A6,1))>31,"",IF(MONTH( >
WORKDAY(A6,1))>MONTH($A$1),"",IF(YEAR(WORKDAY(A6,1 >
))>YEAR($A$1),"",WORKDAY(A6,1)))))

Perhaps there is a missing piece.

Monique



"daddylonglegs" wrote:

>
> SteveG Wrote:
> > Slight typo. You need to change the A1 in the YEAR formula to an
> > absolute reference.
> >
> > =IF(A6="","",IF(DAY(WORKDAY(A6,1))>31,"",IF(MONTH(
> > WORKDAY(A6,1))>MONTH($A$1),"",IF(YEAR(WORKDAY(A6,1
> > ))>YEAR($A$1),"",WORKDAY(A6,1)))))
>
> Hi Steve/Monique
>
> checking IF(DAY(WORKDAY(A6,1))>31 is redundant since it can never be
> true. Won't this formula in B6 copied across be sufficient?
>
> =IF(A6="","",IF(MONTH(WORKDAY(A6,1))<>MONTH(A6),"",WORKDAY(A6,1)))
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=537695
>
>