Simple problem with Dates and Times is driving me nuts

[BingBong]

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

 

[AnimalMagic]

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

I have a few segments of date and time functions, but you have to work
out the calls.

Most time maths require 24 hr format data entries to be made. The date
math is cool too.

I cut and pasted stuff that worked, so some of this is from the MS
template site.

This returns a blank for no entry, and does date math from week starting
date...

=IF($M$5=0,"",$M$5+2)

M5 is the week starting date cell position. Each day is that value plus
whatever days. The week starting date is filled out on another sheet
along with name, etc My time sheet does lookups for some values from
that sheet. The emp enters name and start date, and the sheet carries
per day. For Saturday week starts, the one shown is for Monday.

I have per day time tallies and per week. My per day tallies are
because there are several jobs in a given day.


For each task, I have a start time cell, and a stop time, and a tally
cell that figures the time spent.


per job Tally:


Start time cell: A1 Type: Time, 24Hr format during entry, can appear
in any format.


Stop Time cell: B1 Same as start time.


Tally cell:

=IF((OR(A1="",B1="")),0,IF((A1<B1),((A1-B1)*24)+24,(A1-B1)*24))

My daily tally is merely a sum of all the job tally cells.

=SUM(C1,D1,E1,F1,G1,H1,I1,J1,K1)

My weekly sum is a tally of the dailys. My sheet is 7 day sheets and a
job list/ employee data sheet

=SUM(Monday!G114,Tuesday!G114,Wednesday!G114,Thursday!G114,Friday!G114,Saturday!G114,Sunday!G114)

My sheet pages are named for the days of the week.

I know this is not likely to be what you were looking for, but I
thought it might help a bit. Most of it (my stuff) is very simple stuff.

 

[David Biddulph]

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.