How to calculate the difference between two times - custom

[mmock]

I am looking for a formula that can help me calculate hours worked by addiing
start and end times. I would like it to calculate the time by quarter hours
with 8 minutes being the up or down trigger.

Example "Down" Start at 8:30am - End at 9:37am = 1 hour
Example "Up" Start at 8:30am - End at 9:38am - 1.25 hours

Any assistance would be appreciated.

Thank you!
M.

[bpeltzer]

If the start and end times are in B2 and C2, respectively, then
=24*MROUND((H13-H12),1/96) should do it. (1/96 because there are 96 quarter
hours in a day, so mround rounds to the nearest quarter hour. Multiplying by
24 turns the time value into the number of hours).
--Bruce

"mmock" wrote:

> I am looking for a formula that can help me calculate hours worked by addiing
> start and end times. I would like it to calculate the time by quarter hours
> with 8 minutes being the up or down trigger.
>
> Example "Down" Start at 8:30am - End at 9:37am = 1 hour
> Example "Up" Start at 8:30am - End at 9:38am - 1.25 hours
>
> Any assistance would be appreciated.
>
> Thank you!
> M.

[mmock]

HI Bruce:

Thanks for the reply. Sorry about the delay now, I was away fora few days.
I tried this formula and something I did nust be wrong couse all I get is
#NAME?
I like the theory, maybe you can give me a better example with the start and
end times Ive provided.
Thanks again,
Mark




"bpeltzer" wrote:

> If the start and end times are in B2 and C2, respectively, then
> =24*MROUND((H13-H12),1/96) should do it. (1/96 because there are 96 quarter
> hours in a day, so mround rounds to the nearest quarter hour. Multiplying by
> 24 turns the time value into the number of hours).
> --Bruce
>
> "mmock" wrote:
>
> > I am looking for a formula that can help me calculate hours worked by addiing
> > start and end times. I would like it to calculate the time by quarter hours
> > with 8 minutes being the up or down trigger.
> >
> > Example "Down" Start at 8:30am - End at 9:37am = 1 hour
> > Example "Up" Start at 8:30am - End at 9:38am - 1.25 hours
> >
> > Any assistance would be appreciated.
> >
> > Thank you!
> > M.

[bpeltzer]

MROUND required the Analysis ToolPak Add-in. On the menu bar Tools >
add-ins, select Analysis ToolPak and click OK.

"mmock" wrote:

> HI Bruce:
>
> Thanks for the reply. Sorry about the delay now, I was away fora few days.
> I tried this formula and something I did nust be wrong couse all I get is
> #NAME?
> I like the theory, maybe you can give me a better example with the start and
> end times Ive provided.
> Thanks again,
> Mark
>
>
>
>
> "bpeltzer" wrote:
>
> > If the start and end times are in B2 and C2, respectively, then
> > =24*MROUND((H13-H12),1/96) should do it. (1/96 because there are 96 quarter
> > hours in a day, so mround rounds to the nearest quarter hour. Multiplying by
> > 24 turns the time value into the number of hours).
> > --Bruce
> >
> > "mmock" wrote:
> >
> > > I am looking for a formula that can help me calculate hours worked by addiing
> > > start and end times. I would like it to calculate the time by quarter hours
> > > with 8 minutes being the up or down trigger.
> > >
> > > Example "Down" Start at 8:30am - End at 9:37am = 1 hour
> > > Example "Up" Start at 8:30am - End at 9:38am - 1.25 hours
> > >
> > > Any assistance would be appreciated.
> > >
> > > Thank you!
> > > M.

[mmock]

Bruce:

I did the add on, got the foluma to work but it is not calculating properly.
I used a start time on 8:30 and a stop time of 13:38 and it gave me 6 hours.
I changed the stop time to 13:37 and still got 6 hours???

We are getting there!!
M.


"bpeltzer" wrote:

> MROUND required the Analysis ToolPak Add-in. On the menu bar Tools >
> add-ins, select Analysis ToolPak and click OK.
>
> "mmock" wrote:
>
> > HI Bruce:
> >
> > Thanks for the reply. Sorry about the delay now, I was away fora few days.
> > I tried this formula and something I did nust be wrong couse all I get is
> > #NAME?
> > I like the theory, maybe you can give me a better example with the start and
> > end times Ive provided.
> > Thanks again,
> > Mark
> >
> >
> >
> >
> > "bpeltzer" wrote:
> >
> > > If the start and end times are in B2 and C2, respectively, then
> > > =24*MROUND((H13-H12),1/96) should do it. (1/96 because there are 96 quarter
> > > hours in a day, so mround rounds to the nearest quarter hour. Multiplying by
> > > 24 turns the time value into the number of hours).
> > > --Bruce
> > >
> > > "mmock" wrote:
> > >
> > > > I am looking for a formula that can help me calculate hours worked by addiing
> > > > start and end times. I would like it to calculate the time by quarter hours
> > > > with 8 minutes being the up or down trigger.
> > > >
> > > > Example "Down" Start at 8:30am - End at 9:37am = 1 hour
> > > > Example "Up" Start at 8:30am - End at 9:38am - 1.25 hours
> > > >
> > > > Any assistance would be appreciated.
> > > >
> > > > Thank you!
> > > > M.

[mmock]

Bruce:

I/WE DID IT. I removed the 24* in the front and it worked prefectly. Thank
you for your assistance!
M.


"mmock" wrote:

> Bruce:
>
> I did the add on, got the foluma to work but it is not calculating properly.
> I used a start time on 8:30 and a stop time of 13:38 and it gave me 6 hours.
> I changed the stop time to 13:37 and still got 6 hours???
>
> We are getting there!!
> M.
>
>
> "bpeltzer" wrote:
>
> > MROUND required the Analysis ToolPak Add-in. On the menu bar Tools >
> > add-ins, select Analysis ToolPak and click OK.
> >
> > "mmock" wrote:
> >
> > > HI Bruce:
> > >
> > > Thanks for the reply. Sorry about the delay now, I was away fora few days.
> > > I tried this formula and something I did nust be wrong couse all I get is
> > > #NAME?
> > > I like the theory, maybe you can give me a better example with the start and
> > > end times Ive provided.
> > > Thanks again,
> > > Mark
> > >
> > >
> > >
> > >
> > > "bpeltzer" wrote:
> > >
> > > > If the start and end times are in B2 and C2, respectively, then
> > > > =24*MROUND((H13-H12),1/96) should do it. (1/96 because there are 96 quarter
> > > > hours in a day, so mround rounds to the nearest quarter hour. Multiplying by
> > > > 24 turns the time value into the number of hours).
> > > > --Bruce
> > > >
> > > > "mmock" wrote:
> > > >
> > > > > I am looking for a formula that can help me calculate hours worked by addiing
> > > > > start and end times. I would like it to calculate the time by quarter hours
> > > > > with 8 minutes being the up or down trigger.
> > > > >
> > > > > Example "Down" Start at 8:30am - End at 9:37am = 1 hour
> > > > > Example "Up" Start at 8:30am - End at 9:38am - 1.25 hours
> > > > >
> > > > > Any assistance would be appreciated.
> > > > >
> > > > > Thank you!
> > > > > M.

[bpeltzer]

Hmmm, with the original formula, 8:30 in H12 and 13:37 in H13, I get a result
of 5; with 13:38 in H13 the result is 5.25. Are you entering the times in
the cells that the formula is referencing? What format do you have on the
cell containing the formula (if it doesn't show decimal places, this isn't
going to do much...).

"mmock" wrote:

> Bruce:
>
> I did the add on, got the foluma to work but it is not calculating properly.
> I used a start time on 8:30 and a stop time of 13:38 and it gave me 6 hours.
> I changed the stop time to 13:37 and still got 6 hours???
>
> We are getting there!!
> M.
>
>
> "bpeltzer" wrote:
>
> > MROUND required the Analysis ToolPak Add-in. On the menu bar Tools >
> > add-ins, select Analysis ToolPak and click OK.
> >
> > "mmock" wrote:
> >
> > > HI Bruce:
> > >
> > > Thanks for the reply. Sorry about the delay now, I was away fora few days.
> > > I tried this formula and something I did nust be wrong couse all I get is
> > > #NAME?
> > > I like the theory, maybe you can give me a better example with the start and
> > > end times Ive provided.
> > > Thanks again,
> > > Mark
> > >
> > >
> > >
> > >
> > > "bpeltzer" wrote:
> > >
> > > > If the start and end times are in B2 and C2, respectively, then
> > > > =24*MROUND((H13-H12),1/96) should do it. (1/96 because there are 96 quarter
> > > > hours in a day, so mround rounds to the nearest quarter hour. Multiplying by
> > > > 24 turns the time value into the number of hours).
> > > > --Bruce
> > > >
> > > > "mmock" wrote:
> > > >
> > > > > I am looking for a formula that can help me calculate hours worked by addiing
> > > > > start and end times. I would like it to calculate the time by quarter hours
> > > > > with 8 minutes being the up or down trigger.
> > > > >
> > > > > Example "Down" Start at 8:30am - End at 9:37am = 1 hour
> > > > > Example "Up" Start at 8:30am - End at 9:38am - 1.25 hours
> > > > >
> > > > > Any assistance would be appreciated.
> > > > >
> > > > > Thank you!
> > > > > M.

[evon]

I got a similar question,

Start Time End Time No. of hours
10:30 15:00 4.5
00:00 08:00 8.0
08:00 23:59 16.0

How should i do this?

"bpeltzer" wrote:

> Hmmm, with the original formula, 8:30 in H12 and 13:37 in H13, I get a result
> of 5; with 13:38 in H13 the result is 5.25. Are you entering the times in
> the cells that the formula is referencing? What format do you have on the
> cell containing the formula (if it doesn't show decimal places, this isn't
> going to do much...).
>
> "mmock" wrote:
>
> > Bruce:
> >
> > I did the add on, got the foluma to work but it is not calculating properly.
> > I used a start time on 8:30 and a stop time of 13:38 and it gave me 6 hours.
> > I changed the stop time to 13:37 and still got 6 hours???
> >
> > We are getting there!!
> > M.
> >
> >
> > "bpeltzer" wrote:
> >
> > > MROUND required the Analysis ToolPak Add-in. On the menu bar Tools >
> > > add-ins, select Analysis ToolPak and click OK.
> > >
> > > "mmock" wrote:
> > >
> > > > HI Bruce:
> > > >
> > > > Thanks for the reply. Sorry about the delay now, I was away fora few days.
> > > > I tried this formula and something I did nust be wrong couse all I get is
> > > > #NAME?
> > > > I like the theory, maybe you can give me a better example with the start and
> > > > end times Ive provided.
> > > > Thanks again,
> > > > Mark
> > > >
> > > >
> > > >
> > > >
> > > > "bpeltzer" wrote:
> > > >
> > > > > If the start and end times are in B2 and C2, respectively, then
> > > > > =24*MROUND((H13-H12),1/96) should do it. (1/96 because there are 96 quarter
> > > > > hours in a day, so mround rounds to the nearest quarter hour. Multiplying by
> > > > > 24 turns the time value into the number of hours).
> > > > > --Bruce
> > > > >
> > > > > "mmock" wrote:
> > > > >
> > > > > > I am looking for a formula that can help me calculate hours worked by addiing
> > > > > > start and end times. I would like it to calculate the time by quarter hours
> > > > > > with 8 minutes being the up or down trigger.
> > > > > >
> > > > > > Example "Down" Start at 8:30am - End at 9:37am = 1 hour
> > > > > > Example "Up" Start at 8:30am - End at 9:38am - 1.25 hours
> > > > > >
> > > > > > Any assistance would be appreciated.
> > > > > >
> > > > > > Thank you!
> > > > > > M.