First off, can Excel identify if a date falls on a Saturday or Sunday? If so, I want to write an IF statement for a series of month-ends (i.e. 12/31/2005, 1/31/2006, 2/28/2006...all the way to 4/30/2012) and if that date is a Saturday or Sunday, have it revert back to the previous Friday? For example, 12/31/2005 is a Saturday, and I need it to be 12/30/2005, the prior Friday, for another formula's reference.
Any help would be huge! Thanks in advance.
One way:
=A7-IF(WEEKDAY(A1,2)=6,1,IF(WEEKDAY(A1,2)=7,2,0))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Just a little clarity, what is A7 and A1 referencing?
Thanks for your quick response.
sorry they should all be A1, and that would be where the original date is...
=A1-IF(WEEKDAY(A1,2)=6,1,IF(WEEKDAY(A1,2)=7,2,0))
If A1 only contains a random date ( not necessarily the last day of a month) try
Please Login or Register to view this content.
Got it to work. Thanks for your help!
If you can use Analysis ToolPak AddIn, you could also use,
=WORKDAY(EOMONTH(A1,0)+1,-1)
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks