How to calculate the difference between two times - custom
 

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.

How to calculate the difference between two times - custom
 

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.

How to calculate the difference between two times - custom
 

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.

How to calculate the difference between two times - custom
 

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.

How to calculate the difference between two times - custom
 

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.

How to calculate the difference between two times - custom
 

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.

How to calculate the difference between two times - custom
 

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.

How to calculate the difference between two times - custom
 

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.