dates on/after 09/01/2011 are removing date from other cells

Hi

Please forgive the length as I'm quite new to this.

I'm using a timesheet workbook for working flexi hours. It shows how many hours needed to work that day, you enter start and finish times and it shows if you are plus or minus for that day and every day for a four week period. The dates are changed by the following formula
=DATE(2010,12,20) (in cell H3) you just alter the date here then each subsequent date cell is changed by =H$3+1
=H$3+2
=H$3+3 etc.
The hours to be worked and credit/debit results use the following:-
=IF(B34>NOW(),"",TRUNC(B!H31/60)+(B!H31/60-TRUNC(B!H31/60))*0.6)
=IF(B34>NOW(),"",TRUNC(B!I31/60)+(B!I31/60-TRUNC(B!I31/60))*0.6)
These use data from a second sheet.

This has worked fine for a number of years but now for some reason whenever the date in any cell reaches 09/01/2011 or any date after that, data from the daily hours and credit/debit cells is being removed and leaves blank cells not only on the main sheet but also the second sheet from which the time data is being obtained..

Can anyone help?

post a workbook i cant reproduce that

Hope this worked.

Mondays and Tuesdays are meant to be blank as the person its for is only part time.

To illustrate the problem I have amended the date formula in the last week of the month so you can see what happens on 09/01. It has never happened before.

(There were two macros running, one for printing the sheet and another for clearing your entered times but I have removed them for the post)

edit or make a new post click advanced then use the paperclip icon to attach a sample workbook

your formula in j42 says
=IF(B42>NOW(),"",TRUNC(B!H39/60)+(B!H39/60-TRUNC(B!H39/60))*0.6)
this bit
=IF(B42>NOW(),""
as today is the 8th and b42 the 9th then that formula will give blank

highlite the cells that are wrong and repost
but it wont alter the fact that any date greater than now() or today() will be blank with that formula
there is something strange with this workbook tho,why are the transition formula entry and evaluation boxes checked in options
also i don't understand this
=IF(B!L34<"","",+B!L34) less than "" ?????

ah i did some reading looks like this was written in lotus notes and then used in excel.. now i have no idea how lotus notes work

When the Transition Formula Entry check box is selected, Microsoft Excel allows you to enter and evaluate formulas in a manner more consistent with the behavior of Lotus 1-2-3.

The Transition Formula Entry option allows you to open Lotus 1-2-3 files without losing or changing information. Note that whenever a Lotus 1-2-3 file is opened in Microsoft Excel, these options are automatically turned on

I think I see what you mean so it will remain blank until that date. Then it will show the correct data. But why then is there data showing in cells J45 and K45 (highlighted in yellow on attached original sheet) with the same formula?

=IF(B!L34<"","",+B!L34) less than "" ?????

I didn't write this line or the sheet I'm just trying to fix it. The original creator is no longer around and I am not expertienced in excel...as you can probably tell lol

its something to do with lotus b45 in excel would =0 but its showing as null this whole thing needs reworking into excel

formulas should be like this but i suspect your macro re-writes in lotus format it probably needs fixing for excel

thats what puzzled me i tried using ctrl+; to put todays date in a cell and an it came out as
"=08/01/2011" displayed in formula bar instead of just "08/01/2011"
formatted cell as date and it gave a 1900 date.its all down to this lotus thing i wonder of lotus doesn't go beyond 2010?