Just to clarify, I'm using the date as a notice of when a Report is
due to be returned. The bases is that its due on "the 1st Tuesday of
each month", but specifically its due by 12pm on the 1st Tuesday, so
if the user opened up the file at 2:00pm on the 1st Tuesday, he/she
should see the following months 1st Tuesday date. If the user opened
the file up at 10:00am on the 1st Tuesday, he/she should see the
current days date pop up

Are you sure you want to do it that way? What if the report the person is
checking on is really due "today" and the person is just checking about it
late (in other words, after 12 noon)? You are going to give that person an
extra month just because they are late in checking when to return it.
Perhaps you should consider adding a Date Borrowed or Date Checked Out (or
whatever heading makes sense for your application) column so that the
formula can see if the the current first Tuesday is the correct first
Tuesday to report.

By the way, here is the formula I posted, modified to use the 12-noon
breakpoint...

=IF(NOW()TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+5)+TIME(12,0,0),DATE(YEAR(TODAY()),1+ MONTH(TODAY()),1)-DAY(DATE(YEAR(TODAY()),1+MONTH(TODAY()),1))+8-WEEKDAY(DATE(YEAR(TODAY()),1+MONTH(TODAY()),1)-DAY(DATE(YEAR(TODAY()),1+MONTH(TODAY()),1))+5),TOD AY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+5))

Notice that it now uses the actual current date-time rather than refer to a
cell's content. That should match more closely what you asked for
originally.

Rick