Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workday (Excel 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workday (Excel 2000)

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

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday (Excel 2000)

    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,D ATE(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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workday (Excel 2000)

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

    =Date-WEEKDAY(Date)+1

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday (Excel 2000)

    Sorry Hans!

    It is K1 <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workday (Excel 2000)

    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)

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday (Excel 2000)

    You are good!!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Thanks again!!

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday (Excel 2000)

    Hopefully this will be my last post of the day <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workday (Excel 2000)

    Try these:

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

    and

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

    Whew!

  10. #10
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday (Excel 2000)

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

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday (Excel 2000)

    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(RDa te,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!!!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workday (Excel 2000)

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

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

  13. #13
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday (Excel 2000)

    Thanks again!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •