How to stop excel 2007 automatically changing my date format?

[Damien McBain]

My spreadsheet has cells with the number format "hh:mm" into which
users type start and finish times. When the start and finish times are
on different days (night shift), the input must also contain the date
like "15/5/2010 22:00" to make the sheet calc correctly. In these
cases, excel changes my numberformat from "hh:mm" to "d/m/yy hh:mm"
even though I specifically set it to "hh:mm".
Can this be stopped? The more these new versions of excel try to
anticipate our needs, the more annoying they become!
TIA
Damien
ps I've worked around this with a worksheet_change event which resets
the number format which is mostly OK except when copying then pasting
into more than one range it sets application.cutcopymode to false (ie
I can only paste once per copy).
I'm also aware I can write a function which handles start time >
finish time but I shouldn't have to do that to preserve my formatting!

 

[ozgrid.com]

No need for dates if you use;
=B1-A1+(B1<A1)
Which accounts for Night shifts.


Where A1 is the start time and B1 the end time.

 

[Damien McBain]

No need for dates if you use;
=B1-A1+(B1<A1)
Which accounts for Night shifts.

Where A1 is the start time and B1 the end time.

The formula is more like "=IF(NOT(ISNUMBER(C3)),0,IF(D3-C3<=0,0,(D3-C3-
(E3/1440)+(7.6-$B3)/24)*24))"! I guess I can work that in

Thanks Dave