Time Difference (0:30 - 24:30)

xl_psychic

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
Hi All,

I was trying to convert time my local time (Indian Standard Time) to PST (Pacific Standard Time) in Excel.

Now I can do this by subtraction 13:30 hrs from my time.

However when I tried subtracting 13:30 hrs from 0:30, Excel gives me 13:00 hrs as an output whereas if it is calculated manually the output should be 11:00 hrs.

If I subtract 13:30 hrs from 24:30, Excel gives me the exact value of 11:00 hrs.

Find attached the HTML Screenshot of the same...
Book1
CDEF
400 Hrs0:3013:3013:00
524 Hrs0:3013:3011:00
Sheet1


Can someone let me know the reason and a turnaround for such calculation..

Help Appreciated..
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Subtracting 13:30 from 0:30 gives -13:00 hours.

Subtracting 13:30 from 24:30 gives 11:00 hours.

I'd say you are using the 1904 date system so that negative dates can be displayed and then doing something with the formatting to supress the negative sign in the first case.
 
Upvote 0
This example would help you to understand the problem better..

If it is 11:00 in PST adding 13:30 hours will give me the time in IST which is 00:30 hrs

But when I subtract 13:30 hrs from 00:30 hrs it does not give me 11:00 hrs

Given below is the html output
Book1
DEFG
8Add11:0013:300:30
9Subtract0:3013:3013:00
Sheet1


Help Appreciated..
 
Upvote 0
You can't take the absolute value to account for negative times. You have to add a day if the start time is less than the end time:

Code: 
=E9-F9+(E9<F9)
 
Upvote 0
Bad assumption re: times

You are talking about times as if they are rolling through dates, whereas you are adding and subtracting absolute times ( no dates ). You will only get the result you are wanting if you include date in your data, or do complex formulae, which you won't be able to do until you understand how Excel is doing it's calculations. Have a look at this, and see if it helps:
Book3
ABCDE
1date/timeamount to minusresult
206/Oct/2005 00:3013:3005/Oct/2005 11:00
3as serial number37169.020830.562537168.45833
4
5time only
600:3013:30-13:00
7as serial number0.0208333330.5625-0.541666667
8
9
Sheet3
 
Upvote 0
Thanks for the input guys... On the basis of the same, I shall try and come up with some solution...
 
Upvote 0
If you're only concerned about displaying PST ( as opposed to doing some calculation with the result) then with your local time in A1 use

=A1+"10:30"

format as h:mm
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Calculate Hours Across Different Dates & Times
Replies
6
Views
913
Proulxs
P
G
  • Solved
Rounding UP to the nearest 6 minutes - The difference between two times
2
Replies
14
Views
607
MARK858
MARK858
Z
  • Solved
Formula to find 'Part of the Day' from the Time Column
Replies
8
Views
299
Zee996
Z
B
  • Question
Summate times (hours) over a range without double counting
Replies
0
Views
359
Bhrudwo
B
C
  • Question
ODD DATE & TIME (FORMAT) CALCULATION
Replies
8
Views
865
Tetra201
Tetra201

Forum statistics

Threads
1,214,805
Messages
6,121,664
Members
449,045
Latest member
Marcus05

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top