How to Convert IST to EST in Excel (5 Easy Ways)

Let’s consider a dataset of 6 IST times. Before starting the conversion, we have to calculate the time difference between those two time zones.

Go to Google and type 1 ist to est.
It returns the EST value as 3:30 PM of the previous day, which makes the time difference between the two zones as 9 hours and 30 minutes.

Note:

All the operations of this article are accomplished by using the Microsoft 365 application.

Method 1 – Utilizing Conventional Formula

Steps:

Insert a new column between columns C and D.
Convert the time difference into 9 hours 30 minutes to 9.5 hours.

Select cell E5 and copy the following formula into the cell. Make sure that you input the Absolute Cell Reference for cell D5.

=B5-($D$5/24)

Press Enter.

Double-click on the Fill Handle icon to copy the formula up to cell E10.

You will be able to convert all the IST times into the EST time zone.

Method 2 – Applying the MOD Function

Steps:

Insert a new column between columns C and D.
Convert the time difference into 9 hours 30 minutes to 9.5 hours.

Select cell E5 and copy the following formula into the cell. Ensure that you input the Absolute Cell Reference for cell D5.

=MOD(B5+($D$5/24),1)

Now, press Enter.

Double-click on the Fill Handle icon to copy the formula up to cell E10.

This transforms all the IST times into the EST time.

Method 3 – Using the TIME Function

Steps:

Select cell D5.
Copy the following formula into the cell:

=B5-TIME(9,30,0)

Press Enter.

Double-click on the Fill Handle icon to copy the formula up to cell D10.

You will get all the IST times converted into EST time.

Read More: How to Convert UTC to EST in Excel

Method 4 – Combining IF and TIME Functions

Steps:

Select cell D5.
Copy the following formula into the cell.

=IF(B5-TIME(9,30,0)<0,1+B5-TIME(9,30,0),B5-TIME(9,30,0))

Press Enter.

Double-click on the Fill Handle icon to copy the formula up to cell D10.

You will get the desired results.

Breakdown of the Formula

TIME(9,30,0): The TIME function shows the time value. Here, the function returns at 9:30.

IF(B5-TIME(9,30,0)<0,1+B5-TIME(9,30,0),B5-TIME(9,30,0)): Here, the IF function first checks the logic which means checking whether the deduction value of cell B5 and the value of TIME function is less then Zero (0). If the logic is true, the function will add one with the deduction value. On the other hand, if the logic is false, the function will return only the deduction value. Here, the formula returns only the deduction value which is 8:30 AM.

Read More: How to Convert GMT to IST in Excel

Method 5 – Using IF, ABS, and TIME Functions

Steps:

Select cell D5.
Copy the following formula into the cell.

=IF(B5-TIME(9,30,0)<0,ABS(1+B5-TIME(9,30,0)),B5-TIME(9,30,0))

Press Enter.

Double-click on the Fill Handle icon to copy the formula up to cell D10.

The formula converts IST times into the EST time zone.

Breakdown of the Formula

TIME(9,30,0): The TIME function shows the time value. Here, the function returns at 9:30.

ABS(1+B5-TIME(9,30,0)): The ABS function will show the absolute value of the result of the TIME function. Here, the formula returns at 8:30 AM.

IF(B5-TIME(9,30,0)<0,ABS(1+B5-TIME(9,30,0)),B5-TIME(9,30,0)): Here, the IF function first checks the logic which means checking whether the deduction value of cell B5 and the value of TIME function is less then Zero (0). If the logic is true, the function will add one with the absolute value of the deduction value. On the other hand, if the logic is false, the function will return only the deduction value. Here, the formula returns only the deduction value which is 8:30 AM.