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]

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.

 

[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))))

 

[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

 

[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

 

[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