Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default adding/subtracting time

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=4


Since 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.

TIA

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default adding/subtracting time

It 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)


wrote in message
oups.com...
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=4


Since 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.

TIA



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
kat
 
Posts: n/a
Default adding/subtracting time

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(b1a1,"24:00"-(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
supersolution

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default adding/subtracting time

Hi Kat

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

--
Regards

Roger Govier


"kat" wrote in message
oups.com...
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(b1a1,"24:00"-(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
supersolution



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default adding/subtracting time

I think Roger means

=MOD(B1-A1,1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Roger Govier" wrote in message
...
Hi Kat

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

--
Regards

Roger Govier


"kat" wrote in message
oups.com...
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(b1a1,"24:00"-(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
supersolution







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default adding/subtracting time

Indeed I did.
Thanks for catching the typo, Bob

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I think Roger means

=MOD(B1-A1,1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Roger Govier" wrote in message
...
Hi Kat

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

--
Regards

Roger Govier


"kat" wrote in message
oups.com...
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(b1a1,"24:00"-(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
supersolution







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time log adding time from separate sheets teastman New Users to Excel 1 December 31st 05 05:14 PM
Entering Times Denise Excel Discussion (Misc queries) 9 November 15th 05 05:57 PM
time differences in a column 68magnolia71 Excel Worksheet Functions 3 May 9th 05 09:46 PM
Accumulate weekly time to total time in Excel. delve Excel Discussion (Misc queries) 0 May 4th 05 08:14 PM
Time Sheet Calculation Help Needed! sax30 Excel Worksheet Functions 2 April 26th 05 08:08 PM


All times are GMT +1. The time now is 09:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"