Workday (Excel 2000)

Topic

New Reply

Hello!

Does anyone know if there a way to add something on to the end of an EOMONTH formula that would allow me to determine what the last working day of the previous month was? =EOMONTH($A$2,-1)

Thanks!

Marie

Reply | Quote

Replies

Try this:

=WORKDAY(EOMONTH($A$2,-1)+1,-1)

EOMONTH($A$2,-1) is the last day of the previous month
EOMONTH($A$2,-1)+1 is the first day of the month
WORKDAY(EOMONTH($A$2,-1)+1,-1) is the last workday before the first day of the month, i.e. the last workday of the previous month.

Note for others reading this: these functions require that the Analysis ToolPak has been installed (Tools | Add-Ins…)

Reply | Quote

Hans,

Thanks so much for the help with this. Wondering if you could help with another calculation. I have in Cell I1, a calculation to provide me with a QTD total:

=IF(MONTH(Date)>=10,DATE(YEAR(Date),10,1),IF(MONTH(Date)>=7,DATE(YEAR(Date),7,1),IF(MONTH(Date)>=4,DATE(YEAR(Date),4,1),DATE(YEAR(Date),1,1))))

This worked well until I found out that funding days do not include the last working day of the current month, but do include the last working day of the previous month.

Is there a way that this QTD calculation can be modified to work in this manner?

Thanks again for any assistance that you can provide.

Marie

Reply | Quote

I’m confused. In the spreadsheet you posted, cell I1 in the SBS Originations worksheet contains a completely different formula:

=Date-WEEKDAY(Date)+1

Reply | Quote

Sorry Hans!

It is K1

Reply | Quote

Will this do to calculate the last workday of the month prior to the beginning of the quarter?

=WORKDAY(DATE(YEAR(Date),INT((MONTH(Date)-1)/3)*3+1,1),-1)

Reply | Quote

You are good!!!

Thanks again!!

Reply | Quote

Hopefully this will be my last post of the day

I am using the curent formulas to determine the the BOM(Beginning of the month) =WORKDAY(EOMONTH(RDate,-1)+1,-1)
, and EOM(End of Month) =WORKDAY(EOMONTH(RDate,0)+1,-2)

This worked great until “RDate” became 04/29/05, which would be the first funding day of the next month. Is there a way to modify the formulas so that they will look at “RDate” and then calculate what the beginning and end of month would be?

Marie

Reply | Quote

Try these:

=WORKDAY(EOMONTH(RDATE,(RDATE>WORKDAY(EOMONTH(RDATE,0)+1,-2))-1)+1,-1)

and

=WORKDAY(EOMONTH(RDATE,(RDATE>WORKDAY(EOMONTH(RDATE,0)+1,-2))+0)+1,-2)

Whew!

Reply | Quote

Fabulous!!!! Thanks for letting me pick those brains

Reply | Quote

Hans,

I have another question on this fabulous formula that you created for me so that I could determine the beginning and ending funding date of each month.

5/30 was observed as a holiday (because it fell on Monday). I have defined a range of cells as “Holidays”, and have entered 5/30 n one of the cells. Am hoping that there is a way to add to the original EOM formula (=WORKDAY(EOMONTH(RDate,(RDate>WORKDAY(EOMONTH(RDate,0)+1,-2))+0)+1,-2,) so remove any Holidays that fall between BOM (Beginning of Month) and EOM(End of Month). I did try it some way and it backed the last funding day in the month to the 29th, and it s.b the 27th (as we do nto count weekends).

Thanks for any help that you can provide!!!

Reply | Quote

If you have named the range of cells “Holidays”, you can just plug it into the WORKDAY function:

=WORKDAY(EOMONTH(RDATE,(RDATE>WORKDAY(EOMONTH(RDATE,0)+1,-2,Holidays))+0)+1,-2,Holidays)

Reply | Quote

Thanks again!!!

Reply | Quote