I am trying to do some calculations with Date and Time that should be
simple but I keep getting a #VALUE! result. I hope someone here can
help.

Column A is StartDate
Column B is StartTime
ColumnC is EndDate
Column D is EndTime

Columns A and C are formatted as m/d/yyyy
Columns B and D are formatted as hh:mm AM or PM

Column A starts with the first day of the month in the first row and
each subsequent row is the previous one +1 (A2=A1+1, etc)

Column C is copied from Column A (C1=A1, etc) and there are some
macros that modify this when someone has worked overnight and C1=A1+1

OK, so you would think that this would be the easiest thing in the
world, right?

TimeWorked=((C1+D1)-(A1+B1))*24

The EndTimes aredownloaded from a time clock that formats each
employees time sheet in Excel 5 Tabular format. I am using Excel 2007.
I open the sheet for each employee and cut and paste the EndTime
directly into my sheet and format this column just like column B with
format painter.

The result of the TimeWorked column is #VALUE! and only results in a
number if I manually write in the EndTime. For example, if the result
of the cut and paste is 5:40 PM formatted as Time hh:mm, it will only
result in a number if I manually overwrite this with 5:40 PM.

So the next step is to look "Show Calculation Steps" when I click on
the exclamation point next to the #VALUE!. This shows that the EndDate
is the culprit and causes the TimeWorked eq'n to choke because it
shows up as a 5 digit number as if the format is General. However,
that column is formatted as m/d/yyyy. How can I change this so that I
don't have to manually retype every EndTime?

Thanks in advance

BB