software

I have a spread sheet for tracking employees time and for some days the
fields are displayed correct but in others (using the same format) the hours
are out of whack!
For example:
Cell d7 = 19:30 e7 = 05:30 (should be 10 hours)
Cell d6 = 10:00 e6 = 20:00 (should be 10 hours)
Cell d5 = 05:30 d5 = 15:30 (should be 10 hours)

When I add the totals together it shows 6:30 hours, I've tried several
different formulas from this website and none of them work on this particular
problem.
My spreadsheet uses 2 colums per employee per day (start amp; end) times. It
starts in column B and ends in column O (7 days for the week).

It seems that if the total number of hours for the above 3 employees exceeds
24 hours then the time gets messed up.

Any ideas?

Format the result cell as [h]:mm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;El Beequot; gt; wrote in message
...
gt; I have a spread sheet for tracking employees time and for some days the
gt; fields are displayed correct but in others (using the same format) the
hours
gt; are out of whack!
gt; For example:
gt; Cell d7 = 19:30 e7 = 05:30 (should be 10 hours)
gt; Cell d6 = 10:00 e6 = 20:00 (should be 10 hours)
gt; Cell d5 = 05:30 d5 = 15:30 (should be 10 hours)
gt;
gt; When I add the totals together it shows 6:30 hours, I've tried several
gt; different formulas from this website and none of them work on this
particular
gt; problem.
gt; My spreadsheet uses 2 colums per employee per day (start amp; end) times. It
gt; starts in column B and ends in column O (7 days for the week).
gt;
gt; It seems that if the total number of hours for the above 3 employees
exceeds
gt; 24 hours then the time gets messed up.
gt;
gt; Any ideas?
Hi

With start time p.e. in D5 and end time in E5
=E5-D5 (E5lt;D5)

NB! Both time columns and the column with formula must be formatted in a
valid time format. When you want to sum formula results, format this cell as
custom quot;[h]:mmquot; or quot;[h]:mm:ssquot;Arvi Laanemets
quot;El Beequot; gt; wrote in message
...
gt; I have a spread sheet for tracking employees time and for some days the
gt; fields are displayed correct but in others (using the same format) the
hours
gt; are out of whack!
gt; For example:
gt; Cell d7 = 19:30 e7 = 05:30 (should be 10 hours)
gt; Cell d6 = 10:00 e6 = 20:00 (should be 10 hours)
gt; Cell d5 = 05:30 d5 = 15:30 (should be 10 hours)
gt;
gt; When I add the totals together it shows 6:30 hours, I've tried several
gt; different formulas from this website and none of them work on this
particular
gt; problem.
gt; My spreadsheet uses 2 colums per employee per day (start amp; end) times. It
gt; starts in column B and ends in column O (7 days for the week).
gt;
gt; It seems that if the total number of hours for the above 3 employees
exceeds
gt; 24 hours then the time gets messed up.
gt;
gt; Any ideas?
Here's what I have in the two columns with the total at the bottom.

col D col E
------ ------
StartEnd
5:3015:30
10:0020:00
19:00 5:30

TOTAL6:30

Here's what I get when I change the format to [h]:mm

StartEnd
5:3015:30
10:0020:00
19:00 5:30

TOTAL 606:30

Here's the formula

=MOD(E5-D5,1) MOD(E6-D6,1) MOD(E7-D7,1) MOD(E8-D8,1)
MOD(E9-D9,1) MOD(E10-D10,1) MOD(E11-D11,1)

quot;Arvi Laanemetsquot; wrote:

gt; Hi
gt;
gt; With start time p.e. in D5 and end time in E5
gt; =E5-D5 (E5lt;D5)
gt;
gt; NB! Both time columns and the column with formula must be formatted in a
gt; valid time format. When you want to sum formula results, format this cell as
gt; custom quot;[h]:mmquot; or quot;[h]:mm:ssquot;
gt;
gt;
gt; Arvi Laanemets
gt;
gt;
gt;
gt; quot;El Beequot; gt; wrote in message
gt; ...
gt; gt; I have a spread sheet for tracking employees time and for some days the
gt; gt; fields are displayed correct but in others (using the same format) the
gt; hours
gt; gt; are out of whack!
gt; gt; For example:
gt; gt; Cell d7 = 19:30 e7 = 05:30 (should be 10 hours)
gt; gt; Cell d6 = 10:00 e6 = 20:00 (should be 10 hours)
gt; gt; Cell d5 = 05:30 d5 = 15:30 (should be 10 hours)
gt; gt;
gt; gt; When I add the totals together it shows 6:30 hours, I've tried several
gt; gt; different formulas from this website and none of them work on this
gt; particular
gt; gt; problem.
gt; gt; My spreadsheet uses 2 colums per employee per day (start amp; end) times. It
gt; gt; starts in column B and ends in column O (7 days for the week).
gt; gt;
gt; gt; It seems that if the total number of hours for the above 3 employees
gt; exceeds
gt; gt; 24 hours then the time gets messed up.
gt; gt;
gt; gt; Any ideas?
gt;
gt;
gt;

Arvi,

Disregard the previous email. I found the problem. I had a different
format in one of the cells and the formula had an error; as well.

Thanks for pointing me in the right direction.

quot;Arvi Laanemetsquot; wrote:

gt; Hi
gt;
gt; With start time p.e. in D5 and end time in E5
gt; =E5-D5 (E5lt;D5)
gt;
gt; NB! Both time columns and the column with formula must be formatted in a
gt; valid time format. When you want to sum formula results, format this cell as
gt; custom quot;[h]:mmquot; or quot;[h]:mm:ssquot;
gt;
gt;
gt; Arvi Laanemets
gt;
gt;
gt;
gt; quot;El Beequot; gt; wrote in message
gt; ...
gt; gt; I have a spread sheet for tracking employees time and for some days the
gt; gt; fields are displayed correct but in others (using the same format) the
gt; hours
gt; gt; are out of whack!
gt; gt; For example:
gt; gt; Cell d7 = 19:30 e7 = 05:30 (should be 10 hours)
gt; gt; Cell d6 = 10:00 e6 = 20:00 (should be 10 hours)
gt; gt; Cell d5 = 05:30 d5 = 15:30 (should be 10 hours)
gt; gt;
gt; gt; When I add the totals together it shows 6:30 hours, I've tried several
gt; gt; different formulas from this website and none of them work on this
gt; particular
gt; gt; problem.
gt; gt; My spreadsheet uses 2 colums per employee per day (start amp; end) times. It
gt; gt; starts in column B and ends in column O (7 days for the week).
gt; gt;
gt; gt; It seems that if the total number of hours for the above 3 employees
gt; exceeds
gt; gt; 24 hours then the time gets messed up.
gt; gt;
gt; gt; Any ideas?
gt;
gt;
gt;