# Time Difference Calculation

#### pramodagroiya

##### New Member
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 Number Date Help Topic Source Current Status Last Updated 158635 22-02-2016 09:32 Featured Static Listing Phone Closed 22-02-2016 12:32

<tbody>
</tbody>

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi and welcome to MrExcel,

Try this:

Book1
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
Sheet1 (5)
Cell Formulas
RangeFormula
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
Named Ranges
NameRefers ToCells
'Sheet1 (5)'!holidays='Sheet1 (5)'!\$A\$6:\$A\$12

Last edited:
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

</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

</tbody>

<tbody>
</tbody>

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

</tbody>

<tbody>
</tbody>

HI,

Can you please share the excel

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)

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

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.

Thanks & Regards,
Pramod Kumar

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.

Replies
6
Views
400
Replies
3
Views
471
Replies
17
Views
1K
Replies
0
Views
215
Replies
13
Views
958

1,214,322
Messages
6,118,888
Members
448,856
Latest member
Eduard_Stoo

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

### Which adblocker are you using?

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

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