software

I have a worksheet with times formatted as [$-409]h:mm AM/PM;@. I
don't understand the format and would like to be able to subtract times
to get total hours.

i.e. start time = 2:00 PM and end time= 10:00 PM Hours=8
start time = 10:00 PM and end time= 2:00 AM Hours=4Since I don't understand the current time format (or why they didn't
just use an existing MS format), I don't know how to get my calculation
to work.

TIAIt shouldn't make any difference, it is still just a time value underneath,
so just add them

=A1 B1

--

HTH

RP
(remove nothere from the email address if mailing direct)gt; wrote in message oups.com...
gt; I have a worksheet with times formatted as [$-409]h:mm AM/PM;@. I
gt; don't understand the format and would like to be able to subtract times
gt; to get total hours.
gt;
gt; i.e. start time = 2:00 PM and end time= 10:00 PM Hours=8
gt; start time = 10:00 PM and end time= 2:00 AM Hours=4
gt;
gt;
gt; Since I don't understand the current time format (or why they didn't
gt; just use an existing MS format), I don't know how to get my calculation
gt; to work.
gt;
gt; TIA
gt;
That works great when the time is within the same day, but when it
starts at 10:00PM and ends at 2:00AM I can't calculate the hours.

Currently I just threw out their format and used your solution with the
if function: =IF(b1gt;a1,quot;24:00quot;-(a1-b1),b1-a1) Maybe I overdid it??
but it seems to work fine.

Just curious if anyone has ever come across the weird format I
described in the first post. I thought maybe it was some MVP
supersolutionHi Kat

With start in A1 and End in B1
=MOD(B1,A1,1)
will deal with either scenario

--
Regards

Roger Govierquot;katquot; gt; wrote in message oups.com...
gt; That works great when the time is within the same day, but when it
gt; starts at 10:00PM and ends at 2:00AM I can't calculate the hours.
gt;
gt; Currently I just threw out their format and used your solution with
gt; the
gt; if function: =IF(b1gt;a1,quot;24:00quot;-(a1-b1),b1-a1) Maybe I overdid it??
gt; but it seems to work fine.
gt;
gt; Just curious if anyone has ever come across the weird format I
gt; described in the first post. I thought maybe it was some MVP
gt; supersolution
gt;
I think Roger means

=MOD(B1-A1,1)

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;Roger Govierquot; gt; wrote in message
...
gt; Hi Kat
gt;
gt; With start in A1 and End in B1
gt; =MOD(B1,A1,1)
gt; will deal with either scenario
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;katquot; gt; wrote in message
gt; oups.com...
gt; gt; That works great when the time is within the same day, but when it
gt; gt; starts at 10:00PM and ends at 2:00AM I can't calculate the hours.
gt; gt;
gt; gt; Currently I just threw out their format and used your solution with
gt; gt; the
gt; gt; if function: =IF(b1gt;a1,quot;24:00quot;-(a1-b1),b1-a1) Maybe I overdid it??
gt; gt; but it seems to work fine.
gt; gt;
gt; gt; Just curious if anyone has ever come across the weird format I
gt; gt; described in the first post. I thought maybe it was some MVP
gt; gt; supersolution
gt; gt;
gt;
gt;
Indeed I did.
Thanks for catching the typo, Bob

--
Regards

Roger Govierquot;Bob Phillipsquot; gt; wrote in message
...
gt;I think Roger means
gt;
gt; =MOD(B1-A1,1)
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt; (remove nothere from the email address if mailing direct)
gt;
gt;
gt; quot;Roger Govierquot; gt; wrote in message
gt; ...
gt;gt; Hi Kat
gt;gt;
gt;gt; With start in A1 and End in B1
gt;gt; =MOD(B1,A1,1)
gt;gt; will deal with either scenario
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt;
gt;gt; Roger Govier
gt;gt;
gt;gt;
gt;gt; quot;katquot; gt; wrote in message
gt;gt; oups.com...
gt;gt; gt; That works great when the time is within the same day, but when it
gt;gt; gt; starts at 10:00PM and ends at 2:00AM I can't calculate the hours.
gt;gt; gt;
gt;gt; gt; Currently I just threw out their format and used your solution with
gt;gt; gt; the
gt;gt; gt; if function: =IF(b1gt;a1,quot;24:00quot;-(a1-b1),b1-a1) Maybe I overdid
gt;gt; gt; it??
gt;gt; gt; but it seems to work fine.
gt;gt; gt;
gt;gt; gt; Just curious if anyone has ever come across the weird format I
gt;gt; gt; described in the first post. I thought maybe it was some MVP
gt;gt; gt; supersolution
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;