convert EST date and time to IST date and time through excel.

[chavinash2004]

I have a requirement to convert the date and time through excel.

for example: now the current time is 18:30 (A1) IST and put the formula in excel (A1-"10:30") i got the O/P 8:00.
I am not getting the correct time when the time is 2:00 AM.

Thanks in Advance. Please help in this?

 

[norulen]

Try this

 =IF(A2-TIME(10,30,0)<0,ABS(1+A2-TIME(10,30,0)),A2-TIME(10,30,0))

 

[AOB]

=if(a1-timevalue("10:30")<0,24-(a1-timevalue("10:30")),a1-timevalue("10:30"))

 

[norulen]

Without ABS formula also it will work. i dunno why i added that in the first place :P

 =IF(A2-TIME(10,30,0)<0,1+A2-TIME(10,30,0),A2-TIME(10,30,0))

[/

Try this

 =IF(A2-TIME(10,30,0)<0,ABS(1+A2-TIME(10,30,0)),A2-TIME(10,30,0))

 

[chavinash2004]

Hi,

Thanks for your quick response. The formula is working fine but the problem is i am not able to do it for every minute.
When i pull down increasing with hour. Please verify the below:
[TABLE="width: 138"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]IST[/TD]
[TD]EST[/TD]
[/TR]
[TR]
[TD="align: right"]0:30[/TD]
[TD="align: right"]14:00[/TD]
[/TR]
[TR]
[TD="align: right"]1:30[/TD]
[TD="align: right"]15:00[/TD]
[/TR]
[TR]
[TD="align: right"]2:30[/TD]
[TD="align: right"]16:00[/TD]
[/TR]
[TR]
[TD="align: right"]3:30[/TD]
[TD="align: right"]17:00[/TD]
[/TR]
[TR]
[TD="align: right"]4:30[/TD]
[TD="align: right"]18:00[/TD]
[/TR]
[TR]
[TD="align: right"]5:30[/TD]
[TD="align: right"]19:00[/TD]
[/TR]
[TR]
[TD="align: right"]6:30[/TD]
[TD="align: right"]20:00[/TD]
[/TR]
[TR]
[TD="align: right"]7:30[/TD]
[TD="align: right"]21:00[/TD]
[/TR]
[TR]
[TD="align: right"]8:30[/TD]
[TD="align: right"]22:00[/TD]
[/TR]
[TR]
[TD="align: right"]9:30[/TD]
[TD="align: right"]23:00[/TD]
[/TR]
[TR]
[TD="align: right"]10:30[/TD]
[TD="align: right"]0:00[/TD]
[/TR]
[TR]
[TD="align: right"]11:30[/TD]
[TD="align: right"]1:00[/TD]
[/TR]
[TR]
[TD="align: right"]12:30[/TD]
[TD="align: right"]2:00[/TD]
[/TR]
[TR]
[TD="align: right"]13:30[/TD]
[TD="align: right"]3:00[/TD]
[/TR]
[TR]
[TD="align: right"]14:30[/TD]
[TD="align: right"]4:00[/TD]
[/TR]
[TR]
[TD="align: right"]15:30[/TD]
[TD="align: right"]5:00[/TD]
[/TR]
[TR]
[TD="align: right"]16:30[/TD]
[TD="align: right"]6:00[/TD]
[/TR]
[TR]
[TD="align: right"]17:30[/TD]
[TD="align: right"]7:00[/TD]
[/TR]
[TR]
[TD="align: right"]18:30[/TD]
[TD="align: right"]8:00[/TD]
[/TR]
[TR]
[TD="align: right"]19:30[/TD]
[TD="align: right"]9:00[/TD]
[/TR]
[TR]
[TD="align: right"]20:30[/TD]
[TD="align: right"]10:00[/TD]
[/TR]
[TR]
[TD="align: right"]21:30[/TD]
[TD="align: right"]11:00[/TD]
[/TR]
[TR]
[TD="align: right"]22:30[/TD]
[TD="align: right"]12:00[/TD]
[/TR]
[TR]
[TD="align: right"]23:30[/TD]
[TD="align: right"]13:00[/TD]
[/TR]
</tbody>[/TABLE]

How can do the same for every minute........
Please help me in this also. Thanks in advance. Thanks!! Avinash.

 

[norulen]

Dint this fomula work for you with ABS
=IF(A2-TIME(10,30,0)<0,ABS(1+A2-TIME(10,30,0)),A2-TIME(10,30,0))
Its throwing up the result just as desired by you

BDW what did you mean by every minute??