Right, because we have a date associated with that time. You must have noticed that we don’t have that additional +1 in the formula, why? To get it converted into CST time zone we just need to enter the formula below into B2 and then drag it down. You can simply subtract 6 hours using TIME() function to convert GMT into CST in excel 2016, 2013,and 2010. Generic Formula For (MM/DD/YYYY hh:mm:ss AM/PM) Format =Timestamp - TIME(6,0,0) +1 adds 24 hours to the given time that prevents error.

So when you subtract 6 hours from 5:00 AM it results in an error since there are only 5 hours available. Excel doesn’t know the history before that. When you just enter time in a cell without any date, excel by default takes as date, which is excel’s first date. You subtract 6 hours and you expect to get to 11:00 PM. Let’s say you want to convert 5:0 0 AM into CST. You must be wondering why there is an additional +1. The formula is simply subtracting 6 hours, 0 minutes and 0 seconds to convert GMT to CST. We will use the TIME() function of excel. We just need to subtract 6 hours from A2 to successfully convert GMT to CST. I have GMT timestamps in range A2 to A4 and I want CST time in range B2 to B4. Generic Formula For (hh:mm:ss AM/PM) Format =Timestamp + 1 - TIME(6,0,0)

So, to convert GMT to CST we just need to subtract 6 hours from GMT time. In addition, we need to get the difference in timings in Microsoft Excel.Ĭentral Standard Time (CST) is 6 hours behind Greenwich Mean Time (GMT) or Universal Coordinated Time (UTC), sounds familiar no). While working on Excel reports and Excel dashboards in Microsoft Excel, we need to convert Date and Time.

Time clock converter on excel