Calculation of Time

Closed
ugn10 Posts 48 Registration date Saturday October 27, 2012 Status Member Last seen March 4, 2014 - Mar 6, 2013 at 05:14 AM
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Mar 12, 2013 at 03:08 PM
Hello friends

Pls help me to calculate time in excel.

Time value is A1 is 15:25.
I have to subtract 11:30 hrs from A1 and doing calculation like A1+(24-(11:30)). It's showing error. Pls suggest.

Similarly, I want to add 11:30 hrs to A1. Pls suggest how to do.

Above calculation is based on 24-hr format.
How to convert them in 12-hr format after calculation?

Pls suggest...these are urgent.

Thanking to all



9 replies

Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 6, 2013 at 01:34 PM
Hi ugn10,

Please check the below mentioned link for steps on how to perform calculations on time in Microsoft Excel:

http://office.microsoft.com/en-in/excel-help/calculate-the-difference-between-two-times-HP003056108.aspx

Try these methods and see if it helps.

Do reply with results.
0
ugn10 Posts 48 Registration date Saturday October 27, 2012 Status Member Last seen March 4, 2014
Mar 7, 2013 at 02:10 AM
dear thank u for ur reference....

bt dear...in that reference..calculation is based on cell references like A1+A2...bt my concern is different like A1+11:30 hrs or A1- 11:30....
0
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Mar 7, 2013 at 02:22 AM
Hi ugn10

For example
=A1+"06:15"

=A1-"06:15"

Kevin
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Mar 7, 2013 at 10:49 AM
Hi Ugn10,

Since Kevin solved your calculation issue, only the conversion remains.
This can easily be done by going to cell properties, select Time and select a format with PM behind it.

Best regards,
Trowa
0

Didn't find the answer you are looking for?

Ask a question
ugn10 Posts 48 Registration date Saturday October 27, 2012 Status Member Last seen March 4, 2014
Mar 7, 2013 at 12:13 PM
Kelvin & Trowa...both..thanks a lot dear...

Pls resolve my following issue for which I posted this query.

I will be highly obliged to all of you....pls suggest me to resolve it.

Actually using excel I want to make a formula that can convert any time zone to indian time zone.


For example,

1) New York time is 10.30 hrs behind IST (Indian Standard Time).

If New York time is 2:00 hrs (in cell A1), what formula should be in B1 that can

result IST? is it like A1-"10:30"?...bt it is showing error.

Similarly, if New York time is 22:00 hrs, how to calculate IST?

2) How to do same calculation, XYZ time 14:00 hrs ahead of IST?


With Regards
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 7, 2013 at 05:26 PM
Hi ugn10,

With permission from Kevin & Trowa I would like to help here. I have uploaded a sample. Please check if you need a similar arrangement in Excel. There is a DropDown in the Cell A1 which helps you select different regions, the respective time differences are updated in column `H'. Also, a formula in cell C1 calculates the time difference in IST. The formula in C1 can be extended to add more time zones. You can check the sample file from here:

http://speedy.sh/Yb6fG/Copy-of-Book1-9.xlsx

Do reply with results.
0
ugn10 Posts 48 Registration date Saturday October 27, 2012 Status Member Last seen March 4, 2014
Mar 8, 2013 at 12:15 AM
Hi Zohaib....

Thank you for your assistance.....I downloaded the attachment...bt it is not working in the sheet that I created for conversion.

I have uploaded that sheet...pls check & suggest me.

Link is https://authentification.site/TR74S/TempTime.xlsx

In Current Time column, I want to put any desired time value.
Format column is telling the time difference with IST.

I want to use formula in IST Value column that can calculate any time corresponding to Current Time column.

With my knowledge, I used a formula also but its not working....

Requesting please see & suggest.

Thanks & Regards
GN Jha
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 9, 2013 at 10:59 AM
Your biggest issue is that you don't have time in all cells

but this will work for those cell where you have
local time, difference in time zone
=IF(LEFT(E9,1)="-",E9+("24:00" - MID(F9,2,5)),E9+MID(F9,2,5))

where
E9 has time difference in +13:00 or -13:00 format for example
F9 has the local time

basic idea is that if delta is positive then add time. How ever if delta is negative then change delta by a day
so either go for a custom formula or have values in cells
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 11, 2013 at 11:46 AM
Hi GN Jha and rizvisa1,

First of all I would like to thank rizvisa1 for help and support. Your help is really appreciated. I am sorry GN Jha for I could not reply to you in time.

rizvisa1, it will be a great learning and help for us if you could please incorporate your formula in the sheet GN Jha has attached.

Thanks again for your help.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 12, 2013 at 11:25 AM
The sheet have
Cell E9: Time in Alaska : 2:00 Am
Cell F9: Time difference between Alaska and India time : -14:30
Cell G9: Time in India = =IF(LEFT(F9,1)="-",E9+("24:00" - MID(F9,2,5)),E9+MID(F9,2,5))
0
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 12, 2013 at 03:08 PM
Hi,

Thank you so much rizvisa1, your formula is incredibly helpful. Finally, I made slight changes to the format of the sheet and added your formula to it. Hope it helps GN Jha. Please find the uploaded file here:

http://speedy.sh/8JyXJ/TempTime.xlsx

Do reply with results.
0