function to fill all days of month to end of month
 

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

function to fill all days of month to end of month
 

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(A 1,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

function to fill all days of month to end of month
 

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

function to fill all days of month to end of month
 

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(A 1,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

function to fill all days of month to end of month
 

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

function to fill all days of month to end of month
 

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
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=537695

function to fill all days of month to end of month
 

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

function to fill all days of month to end of month
 

Can you explain the need for:

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

That expression will never be TRUE.

Biff

"SteveG" wrote in
message ...

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

function to fill all days of month to end of month
 

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

function to fill all days of month to end of month
 

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
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=537695

function to fill all days of month to end of month
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=537695

function to fill all days of month to end of month
 

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