What formula can I enter so that excel will always calculate the last working day of the current month? I think I have to use the Workday formula..
Thanks!
2007-01-29 21:48:53 · 6 answers · asked by number_n 2 in Computers & Internet ➔ Software
If the date is in A1, in A2 enter this formula:
=DATE(YEAR(A1),MONTH(A1),31)
and in A3 enter this formula:
=IF(WEEKDAY( IF(MONTH(A1)= MONTH(A2),A2, IF(MONTH(A1)= MONTH(A2-1),A2-1, IF(MONTH(A1)= MONTH(A2-2),A2-2, IF(MONTH(A1)= MONTH(A2-3),A2-3,"error ")))),2)=7, IF(MONTH(A1)= MONTH(A2),A2,IF (MONTH(A1)= MONTH(A2-1),A2-1, IF(MONTH(A1)= MONTH(A2-2),A2-2, IF(MONTH(A1)= MONTH(A2-3), A2-3,"error "))))-2, IF(WEEKDAY( IF(MONTH(A1)= MONTH(A2),A2, IF(MONTH(A1)= MONTH(A2-1),A2-1, IF(MONTH(A1)= MONTH(A2-2),A2-2, IF(MONTH(A1)= MONTH(A2-3), A2-3,"error ")))),2)=6, IF(MONTH(A1)= MONTH(A2),A2, IF(MONTH(A1)= MONTH(A2-1),A2-1, IF(MONTH(A1)= MONTH(A2-2), A2-2,IF(MONTH(A1)= MONTH(A2-3), A2-3,"error "))))-1,IF(MONTH(A1)= MONTH(A2),A2, IF(MONTH(A1)= MONTH(A2-1),A2-1, IF(MONTH(A1)= MONTH(A2-2),A2-2, IF(MONTH(A1)= MONTH(A2-3), A2-3,"error "))))))
It's a bit long, but it works!
Note that if you copy and paste it into a cell, Excel will tell you that there's an errror in the formula that it can correct. It's just removing the spaces that I had to insert so you can read it on Yahoo! Click OK and the formula should work.
Edit: New, shorter formula!
Using the the month in A1 and VBAXLMan's formula in A2:
=DATE(YEAR(A1), MONTH(A1)+1,1)-1
You can put this formula in another cell:
=IF(WEEKDAY (A2)=8,A2-2, IF(WEEKDAY(A2) =7,A2-1,A2))
to get the last workday of the month.
2007-01-30 01:01:07 · answer #1 · answered by nospamcwt 5 · 0⤊ 0⤋
The WORKDAY function is the way to go. I believe this will work. Assume A1 contains the last day of the month (which could also be calculated from any date using EOMONTH function). This simple formula should do the trick:
=WORKDAY(A1+1,-1)
If you want to consider holidays, they should be listed in a range, say D1 to D10, and the formula would be:
=WORKDAY(A1+1,-1,D1:D10)
2007-01-30 08:23:01 · answer #2 · answered by Joliet Jake 3 · 0⤊ 1⤋
Are the numbers to the superb of the / character continually one digit in length? if so, paste this formula in D16 =SUMPRODUCT((N(top(A1:A1000, a million)=top(D15,a million)))*(B1:B1000)) in the adventure that your archives is going previous row one thousand, substitute the only thousand's to a pair selection greater than what you have. The above formula assumes all your archives in column A starts off with g/z.
2016-11-01 21:09:55 · answer #3 · answered by ? 4 · 0⤊ 0⤋
You can do this
=DATE( YEAR(A1), MONTH( A1)+1,1)-1
It get the date of the first of the next month - 1
so it is the last day in the month
Enjoy my profile, I am theVBAXLMan
2007-01-30 20:08:22 · answer #4 · answered by Anonymous · 0⤊ 0⤋
Download this
http://downloads.sgkopi.com/lastworkingday.zip
2007-01-30 15:19:07 · answer #5 · answered by unnga 6 · 0⤊ 1⤋
take a look at this it might help
http://www.excel-vba.com/excel-19-date-time-formulas.htm
2007-01-29 22:00:15 · answer #6 · answered by mzee_wa_kazi 2 · 0⤊ 2⤋