Displaying time - Error in formula (I think?)

[Guest]

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 & 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?

 

[Bob Phillips]

Format the result cell as [h]:mm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Arvi Laanemets]

Hi

With start time p.e. in D5 and end time in E5
=E5-D5+(E5<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 "[h]:mm" or "[h]:mm:ss"


Arvi Laanemets

 

[Guest]

Here's what I have in the two columns with the total at the bottom.

col D col E
------ ------
Start End
5:30 15:30
10:00 20:00
19:00 5:30

TOTAL 6:30

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

Start End
5:30 15:30
10:00 20: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)

Hi

With start time p.e. in D5 and end time in E5
=E5-D5+(E5<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 "[h]:mm" or "[h]:mm:ss"


Arvi Laanemets

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 & 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?

 

[Guest]

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.

Hi

With start time p.e. in D5 and end time in E5
=E5-D5+(E5<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 "[h]:mm" or "[h]:mm:ss"


Arvi Laanemets

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 & 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?