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



Related:

9 responses

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.
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....
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
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
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.
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
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.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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))
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.