Time Difference Calculation

pramodagroiya

New Member
Joined
Feb 23, 2016
Messages
4
Hi,

I have excel sheet, wherein we have ticket raise date and time & ticket close date and time. i need to calculate the time difference if the ticket is closed with 4 hrs to TAT or not. also if the ticket is created after 2:30 PM then the time calculation will be till 6:30 for TAT and rest of the TAT will start next day after 9:30 AM. need to exclude saturday and sunday.

sample file is like this:

Ticket NumberDateHelp TopicSourceCurrent StatusLast Updated
15863522-02-2016 09:32Featured Static ListingPhoneClosed22-02-2016 12:32

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Hi and welcome to MrExcel,

Try this:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Ticket Number</td><td style=";">Date</td><td style=";">Help Topic</td><td style=";">Source</td><td style=";">Current Status</td><td style=";">Last Updated</td><td style="text-align: center;;">TAT</td><td style="text-align: center;;">On Time</td><td style="text-align: right;;"></td><td style=";">Param</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">158635</td><td style="text-align: right;;">22-2-2016 09:32</td><td style=";">Featured Static Listing</td><td style=";">Phone</td><td style=";">Closed</td><td style="text-align: right;;">22-2-2016 12:32</td><td style="text-align: right;;">22-2-2016 13:32</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;"></td><td style=";">Start</td><td style="text-align: right;;">09:30</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">End</td><td style="text-align: right;;">18:30</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">TAT</td><td style="text-align: right;;">04:00</td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1 (5)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G2</th><td style="text-align:left">=WORKDAY(<font color="Blue">B2,CEILING(<font color="Red">(<font color="Green">K4+MOD(<font color="Purple">B2,1</font>)-K2</font>)/(<font color="Green">K3-K2</font>),1</font>)-1,holidays</font>)+MOD(<font color="Blue">B2,1</font>)+K4-CEILING(<font color="Blue">MOD(<font color="Red">B2,1</font>)+K4-K2,K3-K2</font>)+K3-K2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">=F2<G2</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">'Sheet1 (5)'!holidays</th><td style="text-align:left">='Sheet1 (<font color="Blue">5</font>)'!$A$6:$A$12</td></tr></tbody></table></td></tr></table>
 
Last edited:

pramodagroiya

New Member
Joined
Feb 23, 2016
Messages
4
Hi and welcome to MrExcel,

Try this:
ABCDEFGHIJK
1Ticket NumberDateHelp TopicSourceCurrent StatusLast UpdatedTATOn TimeParam
215863522-2-2016 09:32Featured Static ListingPhoneClosed22-2-2016 12:3222-2-2016 13:32TRUEStart09:30
3End18:30
4TAT04:00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1 (5)

Worksheet Formulas
CellFormula
G2=WORKDAY(B2,CEILING((K4+MOD(B2,1)-K2)/(K3-K2),1)-1,holidays)+MOD(B2,1)+K4-CEILING(MOD(B2,1)+K4-K2,K3-K2)+K3-K2
H2=F2<G2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Worksheet Defined Names
NameRefers To
'Sheet1 (5)'!holidays='Sheet1 (5)'!$A$6:$A$12

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

HI,

Can you please share the excel
 

identifyaction

New Member
Joined
Feb 25, 2016
Messages
1
New forum user, experienced excel user and am quite baffled at the moment with a similar problem. We need to calculate date & time differences, simply subtract end time from beginning time as per below sample data.

My colleagues Excel 2011 (I also have the same on my computer) will not calculate TIME data as per below sample data. It keeps coming up with the VALUE error message. It works fine on mine (also excel 2011) and other PC computers. We have done all the common formatting changes, text to columns (but we need the date too) etc. If she enters a simple time value, i.e. 1:08, 2:05 the calculation works fine. Help....




CONTAINER_No LAST_PALLET_SCAN_DATETIME CONTAINER_SEALED_DATETIME Time Difference
BSIU9591920 24/02/2016 01:08:55.393 24/02/2016 02:05:43.446 0:56 (=C2-B2)
 

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284

ADVERTISEMENT

@pramodagroiya, here you go https://app.box.com/s/e1nms5okxbbbuh2lryz1f4o9j5uxr5ed

@ identifyaction, Your Microsoft Windows regional settings will influence how your date/time, numeric, and currency data types appear when you apply formatting options. Windows supports a variety of languages, plus currency and date/time formats for the countries/regions that use those languages. In this case (the format of miliseconds in excel - is in fact a decimal of the seconds) it's dependable on how the settings supports decimals. Check your regional settings and customize the time format so it will be in line with the settings.

I've included a sample based on your sample in the same file as i created for pramodagroiya


Hope this helps.

Please be aware that this is an exemption of me answering this question because your question has nothing to do with the original question. If you want valued responses from the forum it's better to create your own thread. With that you help us help you which ultimately creates a better and more valued response.
 

pramodagroiya

New Member
Joined
Feb 23, 2016
Messages
4
Hi,

Thanks for support, but in given excel its not calculating properly if ticket is raised before 9:30.

E.g: TAT should be calculate even if ticket is raised before 9:30 and after 6:30. in given formula its calculating if ticket is raised at 9:00AM also, ideally it should calculate from 9:30.

please help is correcting.

Thanks & Regards,
Pramod Kumar
 

pramodagroiya

New Member
Joined
Feb 23, 2016
Messages
4
Hi,

Please help in creating this.

Thanks for support, but in given excel its not calculating properly if ticket is raised before 9:30.


E.g: TAT should be calculate even if ticket is raised before 9:30 and after 6:30. in given formula its calculating if ticket is raised at 9:00AM also, ideally it should calculate from 9:30.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top