Displaying time - Error in formula (I think?)

[El Bee]

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)

"El Bee" <ElBee@discussions.microsoft.com> wrote in message
news:B0B9F129-AB44-45B6-B2DE-D766CC1686AB@microsoft.com...
> 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?

[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



"El Bee" <ElBee@discussions.microsoft.com> wrote in message
news:B0B9F129-AB44-45B6-B2DE-D766CC1686AB@microsoft.com...
> 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?

[El Bee]

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)

"Arvi Laanemets" wrote:

> 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
>
>
>
> "El Bee" <ElBee@discussions.microsoft.com> wrote in message
> news:B0B9F129-AB44-45B6-B2DE-D766CC1686AB@microsoft.com...
> > 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?
>
>
>

[El Bee]

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.

"Arvi Laanemets" wrote:

> 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
>
>
>
> "El Bee" <ElBee@discussions.microsoft.com> wrote in message
> news:B0B9F129-AB44-45B6-B2DE-D766CC1686AB@microsoft.com...
> > 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?
>
>
>