Hi Dflak, Thanks for your quick reply. = (B1-A1)*1440. 6. After the ‘if’ word, we’ll start typing out the condition itself – the ‘Start Work’ time (the value in cell C7) is greater than the ‘End Work’ time (the value in cell D7), which is true for the working time that spans midnight. minutes) - (departureTime. Calculate Total Hours Automatically Deducting Lunch Breaks - Excel. Hi I am looking for a way to subtract NOW() from a particular cell which has an example time of 17:23. Using the TEXT Function to Calculate Hours Between Two Times in Excel. My issue is with the first row. =INT (A1)+1 gives me 8/2/2019, but when walking through the formula, it's just 43678 not 43678. Example: Start time: 23:50:00 End time: 00:15:00 How would you formulate an equation to determine the duration of time or differnce between the start and end time? In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. There are two approaches that you can take to present the results in the standard time format (hours : minutes : seconds). We subtract time/dates in excel to get the number of days. It is flexible so it can also be used to normalize non-standard time values as well . For that purpose, you can follow the steps below. Start. Cells (rfnd, cfnd - 2) This issue is when the end time is midnight (ie 0:00AM) The line in blue catches whether the end time is midnight (0:00A), and if it is uses a formula to calculate the number of hours unique to. Time_Out is when they stopped work for whatever reason. From the hour mark to 6 minutes = 0 time paid. Time Difference Formula. Time differences between time zones are simple to work out due to the Universal Time Coordinated (UTC). 30 minutes to 45 minutes = 3/4 hour 45. Here is the formula we will use: =INT( (A2 – B2) * 24) & " hours" //where A2 is EndDate and B2 is the StartDate. ”. If your time runs across midnight in Exc. For example, let’s say your start time is 8:05 AM, and your end time is 12:15 PM. Duration Process :6 hours. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. Here 86400 is the total second in 24 hours and when you enter this formula it will return the difference in start and end time in seconds. Each gets 30 minutes for breaks which is not paid. In this case, select cells B4:D8. 6. Improve this answer. There are two approaches that you can take to present the results in the standard time format (hours : minutes : seconds). The MOD (. Month, today. Re: Calculate time up to midnight. When you subtract a date field from another date field in a calculated column, the result is given in days. Here 86400 is the total second in 24 hours and when you enter this formula it will return the difference in start and end time in seconds. The online date timestamp tool will allow you to enter year, month, day, hour, minus and seconds of the start and the end date time for which you to calculate difference for. Time. The table above is one a I created on Power Bi desktop. 🔗 Steps: Type the following formula on cell D5. You really need to treat schedule_deptime as a date/time value, not just a time value, so that differences across. NOTE. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. There are two approaches that you can take to present the results in the standard time format (hours : minutes : seconds). The arrow ends at the hour circle of an orange dot indicating the apparent place of an astronomical object on the celestial sphere. A star's position is fixed in that system and it will be always at the. Enter start time in A2. Many countries have more than one time zone. For "fixed" stars it will be close, but for the Sun, the rate of change of azimuth varies with time (due to the eccentricity of the Earth's orbit, mostly; and to the fact that angle between the ecliptic and the horizontal system is changing), so the time it takes to traverse azimuths less than 180° will (almost) never be precisely the time it. The DATEDIF formula takes two date cells and calculates the days between them. When B2 and C2 are blank, this formula returns 12:00 AM. Is there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. Thanks! Posted by Aladin Akyurek on July 18, 2001 9:53 AMIs there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. (based on collections) Would like to enter payment and date. I have tried three types of formulas with the same result: =TEXT(F11-E11,"h")Emplyee's can work night shifts (ie across midnight). I have a start time and and an end time and would like to calculate the duration expended between the two. Here is one way: I put the target "sleep at" time in B1. Most of these times are between 9pm and. 30AM to 7. time)*24)+(arr. Easy and fast to calculate timesheets. Formula To Calculate Time Difference Crossing Midnight - Excel. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. Hello everyone! I have to check whether an array of timestamps (half an hour between each timestamp) are within two time values, where it can occur that a set of two time values cross midnight. e. 30am-10pm. 10:43 PM); A2 is when the person woke up (ex. The result shows the current date and time, probably formatted like this: 6/26/2014 9:57. To use the TIME function, simply enter the hour, minute, and second values as arguments in the function. You use the subtraction operator (-) to find the difference between times, and then do either of the. 16 minutes to 30 minutes = 1/2 hour 30. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. Note that according to this approximation cos i equals −1 when the semi-major axis. All works fine until the calculation goes before 24:00. Thus, the hour from midnight to 1 a. Heres the formula which you need to use. It wraps from 59 to 00 so the function correctly returns 1. 46 minutes to 60 minutes = one hour. I'm trying to write a formula that will calculate my employees' total time worked. IF before midnight, there is no need to add 24 hours. A. That is, the range has an inclusive start, and an exclusive end. Cell H45: =J45 - J53 / 2 * H2. I'm struggling with the best way to chart a series of times in Google Spreadsheets that involves a midnight crossover. In astronomy and celestial navigation, the hour angle is the dihedral angle between the meridian plane (containing Earth's axis and the zenith) and. 16 minutes to 30 minutes = 1/2 hour 30. Formula To Calculate Time Difference Crossing Midnight - Excel. To get the correct calculated result between two times over midnight, you can apply the following formula: 1 . Received Date & Time: 10/29/2010 3:27:00 PMRe: "Head Count" of time periods crossing midnight Shout if you want to use formula/Named Range approach to adjust the column ranges automatically. The bread bakes for a total of 50 minutes The oven needs to be pre-heated 1 hour prior to baking. month, day, quarter, year etc. I have a generated excel report that shows day and time in this format: I want to calculate the difference between the two, giving the result in hh:mm:ss. To calculate number of days, hours, minutes and seconds between two dates with time zones. The MOD functions does not work if you use 24 hr / military clock, as it returns 17:30 instead of 06:30 between finish and start. Countdown to Any Date – Create your own countdown. I know @Paul Newcome has skills when it comes to calculating time. For example, if you leave Los Angeles at 09:00 (9:00 am) and arrive in New York at 18:00 (6:00 pm), then the apparent traveling time is 9 hours. Example: Start time: 23:50:00 End time: 00:15:00 How would you formulate an equation to determine the duration of time or differnce between the start and end time? We’ll carry on with the tutorial on how to calculate hours worked after a night shift. 10pm-7. Browse; Search; Sign inIs there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. The only acceptable operators are + and -. Start = 17:00, End = 04:00) you will need to adjust the formula to account for that. Formula To Calculate Time Difference Crossing Midnight - Excel. ; Divide the difference by the average: |a - b| / ((a + b) / 2). By using the ##":"##":"## code it doesn't give me the right answer. Following our example above, 56 hours is our base. to get 10 for a 10 minute difference and 71 for one hour 11 minutes you can use this formula [revised] =MOD (B1-A1,1)*1440. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. Formula To Calculate Time Difference Crossing Midnight - Excel. 30am, 7. Calculate hours between two times after midnight with formula. 5 hours. Start Time (8:00am). someone started at 01:00 end ended at 11:00. Time interval formula To calculate time difference, we convert both time values to seconds since midnight using the following formula: time m = hh*3600 + mm*60 + ss 8PM to 5AM for example. Enter this formula: =(B2-A2+(B2<A2))*24 (A2 is the earlier time, B2 is the later time, you can change them as you need) into a blank cell which beside your time data, see. Duration Process :6 hours. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. Time crossing midnight. 125-0. . The following formula computes the time difference between the mid-day and the time at which the object's shadow equals t times the length of the object itself plus the length of that object's shadow at noon: Thus, in the first four schools of thought, Asr = Dhuhr + A (1), and in Hanafi school, Asr = Dhuhr + A (2). If you have cells with time in Excel, and you subtract one cell from another, you may end up with the pound symbols. To clearly see this, change. Format cell C1 as a duration. Duration Process :6 hours. Local Nav Open Menu Local Nav Close Menu. Calculating Time After Midnight - Hi I am trying to make a formula for a work timesheet. Formula To Calculate Time Difference Crossing Midnight - Excel. Is there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. In order to calculate how much you have earned, we multiply the time (in decimals) by the hourly rate: 7. Hello everyone, I need some help. Find below the sample data and details for your reference. the formula will be in cell B1. Enter this formula: =(B2-A2+(B2<A2))*24 (A2 is the earlier time, B2 is the later time, you can change them as you need) into a blank cell which beside your time data, see. For example, I need to subtract 20:22 (A1) from 20:54 (B1) and get 32 (for minutes) (C1) Also, would the formula be different crossing midnight. When a post goes over midnight, =24* (C30>D30)+D30-C30 will return 17:30 to 11:00 as 23. 30 minutes to 45 minutes = 3/4 hour 45. Formula To Calculate Time Difference Crossing Midnight - Excel. So, when you multiply the time difference by 24, you will get the time duration in hours. =(end_time-start_time)*86400. Rate and speed are similar since they both represent some distance per unit time like miles per hour or kilometers per hour. e. Method 1: Calculate Time Difference Between Two Times by Direct Subtraction. Duration Process :6 hours. Start. Present the result in the standard time format. If you have cells with time in Excel, and you subtract one cell from another, you may end up with the pound symbols. We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows: From the hour mark to 6 minutes = 0 time paid. And because they are numbers, you can add and subtract times just as any other numerical values. You can dramatically simplify things by using date values that contain times (“datetimes”), as explained below. In one cell I enter the "start time", i. , numbers of seconds after midnight of that same. In the Group By dialog box, set the field to be grouped as “Name”, the new column name to “Time Worked”, and set. Note: AM is used for times in the night and morning. Type in. Start Time (8:00am)Calculate hours between two times after midnight with formula. The Time Duration Calculator will calculate the time that has elapsed/difference between. If start and end times span midnight, you will need to adjust the formula as explained below. 1. There are three different time value sets called G1, G2 and G3. Most entries fall within the same day's 24 hour limit, so a simple forumla "=b1-a1" will do. Keeping the format as h:mm means the result corrupts once it reaches 24:00. I cannot figure out the correct formula to complete this. All three arguments are required: Start_date - the initial date of the period you want to calculate. So, if start times are in A and end times are in B (starting row 2), try this: = ( (B2-A2)+ (A2>B2))*24. Formulas: Results:. while i am able to identify when the shift crosses midnight, i am not able to correctly calculate the total number of hours, as 00:00 is tricky, and every formula i tried for hours before midnight, when applied to hours after midnight, throws everything off, and vice-versa. You use the subtraction operator (-) to find the difference between times, and then do either of the. Time Card Calculator Free Online Timecard Calculator. . so 1 = 24hrs exactly. Register. Example. =IF ( (end-start)<0, (end-start)*24+24, (end-start)*24) If you want to see hours and minutes you'd need to. A few are at or after midnight as in Column F. The first question is easily answered: if the end time is before the start time, the time span has crossed midnight. 25 hours for 22:55 till 06:20am and for some reason it's not multiplying the £/hour with the result properly now (giving £1. Hello everyone, I need some help. For example, to calculate hours between 9 PM and 3 AM: =MOD(0. IF after midnight, 24 hours are added to the finish time to allow the calculation of Finish time minus Start Time to still work. Formula To Calculate Time Difference Crossing Midnight - Excel. Now just subtract the Start Work time by using the. End Time (B1): 5:00 PM. Time. As you can see from the formula the night end (ne) has been adjusted to consider the next day. The difference of the sidereal day to the mean solar day is. The last part of the sum is to multiple the result from that formula by the number of people I have on shift. 46 minutes to 60 minutes = one hour. DAYS function. Formula To Calculate Time Difference Crossing Midnight - Excel. To get the correct calculated result between two times over midnight, you can apply the following formula: 1. Excel will now work as follows. 1. Example: Start time: 23:50:00 End time: 00:15:00 How would you formulate an equation to determine the duration of time or differnce between the start and end time? In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. Now, as previously discussed the time difference is based on the day shift. Please Login or Register to view this content. Time. However, I encountered a problem when a bike ride spans across midnight to the next day, as the formula produced a negative value. Formula To Calculate Time Difference Crossing Midnight - Excel. =IF ( (end-start)<0, (end-start)*24+24, (end-start)*24) If you want to see hours and minutes you'd need to modify it to pick. Here's a solution that supports finding the difference even if the end time is less than the start time (over midnight interval) such as 23:55:00-00:25:00 (a half an hour duration):In the example shown, "lower" is lower bound for the period of interest, and "upper" is the upper bound. The formula in E5 works in two parts, using IF to control flow. Declare @aDate DateTime Set @aDate = getDate () Declare @Minutes Integer Set @minutes = 600 -- 10 hours Select DateAdd (day, DateDiff (day, 0, @aDate), 0) + @minutes / 1440. You can then take divide that by 60 to get the hours that have passed, and take. getting the difference bewtween two times accounting for midnight cross overCalculating time past midnight. Enter this formula: =(B2-A2+(B2<A2))*24 ( A2 is the earlier time, B2 is the later time, you can. You can use the TIME function to add or subtract up to 60 seconds: =Start time + TIME (0, 0, N seconds) For example, add 30 seconds: =A2+TIME (0,0,30) Or substitute 30 seconds: =A2-TIME (0,0,30) To calculate over 60 seconds, use simple maths:As you can see in image below : SECOND ROW = 40 minutes. As such, you may want a column for "total hours" to make a few simple formulae for calculating (and makes checking a bit simpler). Second: Returns a second value from a DateTime value: Time. The above formula will give you the total number of hours elapsed between the two-time values. I have 2 times that may cross midnight. Excel Formulas; crossing midnight; Posted by Julie on July 18, 2001 9:43 AM. The formula in previous post is only available for Excel 365/2021 users This formula is available for all Excel-users. =INT (A1)+1 gives me 8/2/2019, but when walking through the formula, it's just 43678 not 43678. The detailed steps follow below: Select the cell (s) you want to format. 2. Time crossing midnight; Results 1 to 5 of 5 Time crossing midnight. DAYS360. I'm using the 4 digit military time and also I need the formula to calculate across midnight. If the first number is 16:51(4:51 pm) and the second is 7:52 (7:52 am the next day) I get #### for the return because Excel is seeing it as a negative number. Duration Process :6 hours. 3. It will replace End Times with 17:00 and Start Times with 09:00 if the End Time is later than 5 PM or the Start Time is earlier than 9 AM. To avoid these problems, you should always treat time intervals as half-open intervals. Start. Formula =(B1-A1)*24 Explained. To calculate the percentage difference between two numbers, a and b, perform the following calculations: Find the absolute difference between two numbers: |a - b|. Write the formula in cell C2. e. This particular example calculates the number of hours between the starting time in cell A2 and the ending time in cell B2, assuming midnight occurs between these two times. Subtracting 30 Minutes From Time In And Out For Schedule - Excel. Note that I didn't use =E24*24, I used the original values. Is there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. 30 minutes to 45 minutes = 3/4 hour 45. In the case of Monday (E4<B4) evaluates to TRUE, and since TRUE = 1 it adds 1 to E4-B4 to correctly calculate the time. Calculate the Longitude in Time Units. The first question is easily answered: if the end time is before the start time, the time span has crossed midnight. The Problem: This is the formula I wrote to subtract these 2 ColumnsThe problem is that *your data structure is wrong*. Most of the work in this formula is done by the TEXT function, which applies a custom number format for hours and minutes to a value created by subtracting the start date from the end date. Duration Process :6 hours. 2 Use of the NOW Function. 8 (80% of the difference). In one cell I enter the "start time", i. For each 6 hours, use 1 minute for the acceleration of time: = 1 minute. Or can the calculation be made on a date/time stamp for each column? It would never be. Hi there, I have broken this userform out of a larger program. Formula To Calculate Time Difference Crossing Midnight - Excel. Note: if your times are entered with the date. The average time between 1am and 11pm is 12 noon, because the measure starts from 12 midnight. Calculate hours between two times after midnight with formula. In Excel I have been trying to find an easier way to calculate a time difference where the times. ToRecord: Returns a record. This way, if you have more than 1 day difference, you're not just adding 24 hours. Formula To Calculate Time Difference Crossing Midnight - Excel. 73, but your absolulte returns 6. Hello All! File & Photo are attached below. Calculate Total Hours Automatically Deducting Lunch Breaks - Excel. 3. First, we’ll use the CDate function of VBA to convert the strings to times. 1:00 am. e. Start. They work on shifts and the operation is 24 hours. Heres the formula which you. hours after midnight dont need a calculation as every day starts counting from 0 so 2 o'clock is also 2 hours after midnight only posible AM/PM notations needs to be omitted to show 1PM as 13:00 after midnight see attachment for result. About Time Difference Calculator. Get the time at any given coordinate on Earth, calculate time zone conversions. we will say the number in cell A1 is 193. One way which results in hours and decimal of an hour is. Browse; Search; Sign in From the hour mark to 6 minutes = 0 time paid. =(end_time-start_time)*86400. StartOfHour: Returns the start of the hour. With. Formula To Calculate Time Difference Crossing Midnight - Excel. First of all, you can calculate the difference between two times by direct substitution. In your selected cell, type the following formula and press Enter. Formula To Calculate Time Difference Crossing Midnight - Excel. 6. The main function. Start Time (8:00am). I'd like the time difference to be in minutes (but that can be done in a separate column formula). Right click the selected cells and then click Format Cells, or press Ctrl + 1. Is there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. I have a solution based on A clever formula from Daniel Maher that will calculate time within a period. 000000. To clearly see this, change the number format of the values in cell A2, B2 and C2 to General. By belle08 in forum Excel General Replies: 4 Last Post: 01-27-2014, 03:47 PM. The amount of the time span that occurred on the date is the time between the start time and midnight. 46 minutes to 60 minutes = one hour. So I used TIME to add or subtract hours to give a GMT hour (in my columns I and J). Not sure if there's a formula or if it needs the help of a macro. Since these times span from one day to the next, I am finding myself unable to create a formula that properly returns the result (A3) of 7:15. Example: Start time: 23:50:00 End time: 00:15:00 How would you formulate an equation to determine the duration of time or differnce between the start and end time? We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows: From the hour mark to 6 minutes = 0 time paid. Example: Start time: 23:50:00 End time: 00:15:00 How would you formulate an equation to determine the duration of time or differnce between the start and end time? Get Difference in Seconds. The following formulas give ways to calculate the net Profit or Loss and how to display that information when using the Spreadsheet System for Trading. Is there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. =(MOD([@[Actual Time End]]-[@[Actual Time Start]],1))*24 This gives me the [Total Actual Time Hours] For the Actual Time End and Actual Time Start When the employee DIDN'T show up we entered 0 for both cells with the same cell format Number -> 2 decimal places. Calculating Time Difference Between Two Days Combined With IF Formula? Calculating Date & Time Difference: Result In Hours Only; Mean By Day Of The Week If After A Certain Time And Before Midnight. Is there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. Example:. Here you can see the same difference, one formatted as automatic, which defaults to time, the other is formatted as a duration: The formula =B2-A2+(B2<A2) returns the difference between the two times, and adds 1 day if minuend is less than the subtrahend, it adds 0 days otherwise. e. In cell C1, enter the formula: =B1 - A1. Is there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. Duration Process :6 hours. For example. 45 as the difference in midnight minus 8/1/2019 1:10 PM. Format cells to get average time. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. A pure date is stored as a date as of crossing midnight (time of 0). On Wed, 19 May 2010 08:59:01 -0700, Norm wrote: I have read all the other threads on this subject and they work fine for me untill I need to cross midnight and the value is the time subtracted from 24 here are the cells: Date Store Actual Arrival Time Est Arrival Time Variance 4/6/2010 33369 11:26:00 PM 12:27:00 AM -22:59 Can someone let me know. With the “Name” field selected, click Transform (tab) -> Table (group) -> Group By. That means we didn’t show you the output if your end time is on a different date. Over 7 minutes to 15 minutes = 1/4 hour 15. Over 7 minutes to 15 minutes = 1/4 hour 15. when i tested both formula i am little bit confused why there is a difference Ali time shows - 02:12 Glen times shows - 2. =B2-A2+ (A2>B2) That will work to calculate time between A2 and B2 even if it passes midnight. The problem is it goes haywire when you. ) $endgroup$If I wanted to subtract two military times to get minutes what formula would I use. " In this case both times belong to the same date and wake_time>bed_time. . - Free Excel Help. As a general rule of thumb, a change of 15° of longitude should result in a time difference of 1 hour. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. 83 or something like that. mailrail: Excel Discussion (Misc queries) 3: April 18th 07 07:39 PM: Calculate hours and minutes between times when it goes past midnig: SC_Dave: Excel Worksheet Functions: 9: March 23rd 07 07:19 PM: formula to calculate time difference crossing midnight: ditorejax: Excel Worksheet Functions: 3: August 17th 06. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. g. Similarly, we do the adjustment for the end time, calculating the effective end time (eEff) considering the next day when the end time (end) is lower than the start time (start). Please Login or Register to view this content. Thank you for the quick reply bebo021999. So given the following. After that, simply press Enter on your keyboard, and you will get 7:00 AM as the return of the subtraction formula. I need to subtract hours or minutes from a given time. Hi, I using this formals to calculate the night shift hours Calculation Start 18pm to 5am anything after 11hrs won. , there are a total of 9 time zones used. You can do it this way: select *, convert (time, convert (datetime, timeto) - convert (datetime, timefrom)) from table1. Start. "Midnight" is 00:00:00 and is only recognized as belonging to the following day. The formula would then be something like: =((arr. Here's an example of how to calculate a time difference in Google Sheets: Start Time (A1): 8:00 AM. The formula in cell C2 is =B2-A2 and the formula for the averages in cell A7 is =AVERAGE (A2:A6) Custom number formatting for Column C: uses h "hrs" mm "min" and Columns A-B use m/d/yyyy h:mm AM/PM. Start. Full 24 examples for 2 time zones. In this. Looking at this example, you might wonder why C10 works, but C11 doesn’t. Therefore, dividing the (positive) difference of the SAS time values (i. I am trying to automatically track the duration of time in minutes that has elapsed between the start time and end time. Example: Start time: 23:50:00 End time: 00:15:00 How would you formulate an equation to determine the duration of time or differnce between the start and end time?There are several ways to calculate the difference between two times. I need to be able to work out the amount of worked time that fits into a certain time period. (Among the things I'll be doing is comparing those values the the corresponding values for successive mean solar days. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. Type the other start and end times for your friends Joy and. Below formula take in to account the time out before/after midnight. This will convert the times to datetime for day 0 (1. Is there any formula to calculate Start time to End time with excluding lunch time? Here is example : Lunch time :12:00pm to 1:00pm. Next press Enter. In this video, you will see the easiest way to find the time difference between two dates and times that go past midnight. Calculate Elapsed Time in Excel by Subtracting Two Date Values. This works great and gives me the hours worked in a day until I enter a value after midnight for the end time. New comments cannot be posted and votes cannot be cast. These time zones encompass most of the contiguous United. In Excel I have been trying to find an easier way to calculate a time difference where the times cross midnight. 999. I need a formula to calculate Turn Around Time. If this is the case, then you have two amounts to. Follow.