Calculation of Time

[Closed]
Report
Posts
48
Registration date
Saturday October 27, 2012
Status
Member
Last seen
March 4, 2014
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
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

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
43
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.
Posts
48
Registration date
Saturday October 27, 2012
Status
Member
Last seen
March 4, 2014

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....
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
Hi ugn10

For example
=A1+"06:15"

=A1-"06:15"

Kevin
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
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
Posts
48
Registration date
Saturday October 27, 2012
Status
Member
Last seen
March 4, 2014

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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
43
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.
Posts
48
Registration date
Saturday October 27, 2012
Status
Member
Last seen
March 4, 2014

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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
43
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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))
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
43
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.