Tiem Calculation

[Randy]

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.

 

[Bob Phillips]

=N(MOD(F3-E3,1)*24)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peo Sjoblom]

=(B2-A2)*24

formatted as general




--


Regards,


Peo Sjoblom

 

[JLatham]

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.

 

[Gary''s Student]

Hi Randy:

1. always remember to put a single space between the time and the am/pm when
typing data. So type:

11:00 am
and not:
11:00am

2. In A1 and B1 enter:
11:00 PM 1:00 AM
and in C1 enter:

=IF(B1<A1,B1+1-A1,B1-A1)

click on C1 and:

Format > Cells... > Number > Custom > h

and you will see the 2

You need this type of formula in C1 if you start before midnight and end
after midnight.

 

[Randy]

Thank you, thank you, thank you! Worked like a charm! YOU ROCK!
--
Randy Street
Rancho Cucamonga, CA

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.

 

[JLatham]

Glad I ended up in line first. Thanks for the feedback.

You might check out Bob Phillips, Peo Sjoblom and Gary"s Student replies as
they all offer something and you may find you prefer one of their solutions
to the one I offered.

Thank you, thank you, thank you! Worked like a charm! YOU ROCK!
--
Randy Street
Rancho Cucamonga, CA

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.

 

[Randy]

Hi J...you are welcome for the feedback...I have another questions for
ya....How can I incorporate something to the effect that if the value of your
formula =IF(B1<A1,1-ABS(B1-A1),B1-A1) is less than 2:00, to set the value at
2:00?
--
Randy Street
Rancho Cucamonga, CA

Glad I ended up in line first. Thanks for the feedback.

You might check out Bob Phillips, Peo Sjoblom and Gary"s Student replies as
they all offer something and you may find you prefer one of their solutions
to the one I offered.

Thank you, thank you, thank you! Worked like a charm! YOU ROCK!
--
Randy Street
Rancho Cucamonga, CA

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.


:

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.

 

[JLatham]

I think if you'll just wrap all of that up in a MAX() function, it'll do the
trick:

=MAX(2/24, IF(B1<A1,1-ABS(B1-A1),B1-A1))
So if the result is less than 2, it will display as 2, while if the result
is greater than 2 hours, it'll show that.

You can change 2/24 to 1/12 (same value, just simplified). I used 2/24
because it more clearly shows that you're looking for a value that is 2 of 24
hours.

Using that formula, your case of 10 pm - 11 pm will show up as 2 instead of 1.

Hi J...you are welcome for the feedback...I have another questions for
ya....How can I incorporate something to the effect that if the value of your
formula =IF(B1<A1,1-ABS(B1-A1),B1-A1) is less than 2:00, to set the value at
2:00?
--
Randy Street
Rancho Cucamonga, CA

Glad I ended up in line first. Thanks for the feedback.

You might check out Bob Phillips, Peo Sjoblom and Gary"s Student replies as
they all offer something and you may find you prefer one of their solutions
to the one I offered.

Thank you, thank you, thank you! Worked like a charm! YOU ROCK!
--
Randy Street
Rancho Cucamonga, CA


:

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.


:

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.