Soumik Dutta
In our world, we have different types of time zone, and almost every country has its own standard time. Among them, Indian Standard Time (IST) and Eastern Standard Time (EST) are mentionable. In this article, we will demonstrate to you 5 handy methods to convert IST to EST in Excel. If you are also curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
What Is IST?
The full form of IST is Indian Standard Time. This time scale is 5:30 hours ahead of the Coordinate Universal Time (UTC). The timezone is a half an hour timezone which means the local time of this timezone differs by 30 minutes. It is the standard timezone used in Asia.
What Is EST?
EST stands for Eastern Standard Time and the time scale is 5 hours behind the Coordinate Universal Time (UTC). This timezone is widely used in North America, Central America, and the Caribbean region.
5 Easy Methods to Convert IST to EST in Excel
To consider the approaches, we consider a dataset of 6 IST time in our dataset. Before starting the conversion, we have to estimate the time difference between those two time zones.
For that, go to Google and type 1 ist to est.
It returns the EST timezone value as 3:30 PM of the previous day which illustrates the time difference between the two zones at 9 hours and 30 minutes.
📚 Note:
All the operations of this article are accomplished by using the Microsoft Office 365 application.
1. Utilizing Conventional Formula
In this method, we are going to use a simple conventional formula to convert time from IST to EST. The steps of this process are given below:
📌 Steps:
- First of all, insert a new column between columns C and D.
- Now, convert the time difference into 9 hours 30 minutes to 9.5 hours.
- Then, select cell E5 and write down the following formula into the cell. Make sure that you input the Absolute Cell Reference for cell D5.
=B5-($D$5/24)
- Press Enter.
- After that, double-click on the Fill Handle icon to copy the formula up to cell E10.
- You will be able to convert all the IST time into the EST timezone.
Thus, we can say that our formula to convert the time zone works perfectly, and we are able to convert the IST timezone to the EST timezone.
2. Applying MOD Function
In this process, we will use the MOD function to convert time from IST to EST. The steps of this approach are given as follows:
📌 Steps:
- First, insert a new column between columns C and D.
- Then, convert the time difference into 9 hours 30 minutes to 9.5 hours.
- After that, select cell E5 and write down 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.
- Next, double-click on the Fill Handle icon to copy the formula up to cell E10.
- You will be able to transform all the IST time into the EST time.
Hence, we can say that our formula to convert the time zone works effectively, and we are able to convert the IST timezone to the EST timezone.
Read More: How to Create World Time Zone Clock in Excel (2 Easy Methods)
3. Using TIME Function
In this approach, the TIME function will help us to convert time from the IST timezone to the EST timezone. The steps of this procedure are explained as follows:
📌 Steps:
- At first, select cell D5.
- Now, write down the following formula into the cell.
=B5-TIME(9,30,0)
- Press Enter.
- Then, double-click on the Fill Handle icon to copy the formula up to cell D10.
- You will get all the IST time converted into EST time.
Therefore, we can say that our formula to convert the time zone works precisely, and we are able to convert the IST timezone to the EST timezone.
Read More: How to Convert UTC to EST in Excel (3 Easy Ways)
4. Combining IF and TIME Functions
In the following procedure, we are going to use the IF and TIME functions to convert time from the IST timezone to the EST timezone. The procedure is described below step by step::
📌 Steps:
- Firstly, select cell D5.
- Next, write down the following formula into the cell.
=IF(B5-TIME(9,30,0)<0,1+B5-TIME(9,30,0),B5-TIME(9,30,0))
- Now, press Enter.
- Then, double-click on the Fill Handle icon to copy the formula up to cell D10.
- You will get the desired results.
So, we can say that our formula to convert the time zone works perfectly, and we are able to convert the IST timezone to the EST timezone.
🔎 Breakdown of the Formula
We are breaking down the formula for cell D5.
👉 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 EST in Excel (4 Quick Ways)
5. Using IF, ABS, and TIME Functions
In the final method, we will use the IF, ABS, and TIME functions to convert time from the IST timezone to the EST timezone. The step-by-step process of this method is described as follows:
📌 Steps:
- In the beginning, select cell D5.
- After that, write down 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.
- Now, double-click on the Fill Handle icon to copy the formula up to cell D10.
- You will figure out all the converted IST time into the EST timezone.
Finally, we can say that our formula to convert the time zone works successfully, and we are able to convert the IST timezone to the EST timezone.
🔎 Breakdown of the Formula
We are breaking down the formula for cell D5.
👉 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: Making a List of Countries by Time Zone in Excel (with Easy Steps)
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to convert IST to EST in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!
