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.

Estimating the time difference between two time zones

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.

Inserting new column to use formula

  • 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.

Utilizing Conventional Formula to Convert Time Value

  • 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.

Utilizing Conventional Formula to Convert IST to EST


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.

Adding new column to convert time

  • 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.

Applying MOD Function to Convert Time

  • 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.

Applying MOD Function to Convert IST to EST


Method 3 – Using the TIME Function

Steps:

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

=B5-TIME(9,30,0)

  • Press Enter.

Using TIME Function to Convert the Time Value

  • 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.

Using TIME Function to Convert IST to EST

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.

Combining IF and TIME Functions to Convert Time Value

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

  • You will get the desired results.

Combining IF and TIME Functions to Convert IST to EST

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.

Using IF, ABS, and TIME Functions to Convert Time Value

  • 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.

Using IF, ABS, and TIME Functions to Convert IST to EST

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.

Read More: Convert Time Zone with Daylight Savings in Excel


Download the Practice Workbook


<< Go Back to Time Zone in Excel | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo