MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula!!!! - Day of last market close


Posted by Super-T on December 10, 2001 8:14 PM

=IF(0.6464>VALUE(MID(TEXT(NOW(),"0.000"),6,10)),(WORKDAY(TODAY(),-1)),WORKDAY(TODAY(),0))


Posted by IML on December 11, 2001 6:39 AM

bolean

Also
=WORKDAY(TODAY(),-1)+((NOW()-INT(NOW()))>TIMEVALUE("15:30:00"))

where the timevalue is provided to allow users to easily change the local stock market closing time.


Posted by IML on December 14, 2001 2:55 PM

spelling and formula error

okay Boolean and both formula provide the incorrect answer on weekends after market close

I believe
=WORKDAY(TODAY(),-1*OR(WEEKDAY(TODAY())={7,1},(NOW()-INT(NOW()))<TIMEVALUE("15:30:00"),COUNTIF(holiday,TODAY())=1),holiday)

addresses this with the holiday being the named range for holidays.