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

Start by checking which of your cells is really causing the problem.
What does =ISNUMBER(A1) say?
Similarly ISNUMBER(B1) ...(C1) and ---(D1).
If you don't get a TRUE from ISNUMBER, that's where your problem is.
My guess is that you've got text, rather than a real date or time, in some
of your cells.

Another clue is that if you format cells temporarily to General, the ones
which are text won't change.
Having identified which are text, you'll need to look for extraneous spaces
or other stray non-printing characters.
--
David Biddulph

"BingBong" wrote in message
...
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