Calculate number of hours and minutes between 2 date and time

**aravindhan_31** · 11-17-2019, 01:29 PM

Hi,

I have a query start date & time as 26-09-2019 13:28 & query solved date and time as 27-09-2019 13:48 in cells A4 & B4. I work in a shift that starts at 12:30 PM( named as lower) PM to 9:30 PM(named as upper). To find the duration between these start and end time within my shift timing I use this formula and that works perfectly and gives me a result of 9:20 as duration

=IF(B4="","",(NETWORKDAYS(A4,B4)-1)*(Upper-Lower)
+IF(NETWORKDAYS(B4,B4),MEDIAN(MOD(B4,1),Upper,Lower),Upper)
-MEDIAN(NETWORKDAYS(A4,A4)*MOD(A4,1),Upper,Lower))

link:https://exceljet.net/formula/get-wor...ates-and-times

But, if start date or end date falls on a week end then this gives me 0:00:00 due to networkdays formula

eg
29-09-2019 13:28 & query solved date and time as 30-09-2019 13:48 the result of the formula is 0, can someone please help me with formula that gives the duration for week ends as well.

thanks for the help in advance
Regards
Arvind

**menem** · 11-18-2019, 03:13 AM

Beware!! My english is not so well , so I may mis-understand your request. >_<

Please try to change/remove this part and check the result.

=IF(B4="","",(NETWORKDAYS(A4,B4)-1)*(Upper-Lower)
+IF(NETWORKDAYS(B4,B4),MEDIAN(MOD(B4,1),Upper,Lower),Upper)
-MEDIAN(NETWORKDAYS(A4,A4)*MOD(A4,1),Upper,Lower))

will be
=IF(B4="","",(B4-A4-1)*(Upper-Lower)
+MEDIAN(MOD(B4,1),Upper,Lower)
-MEDIAN(MOD(A4,1),Upper,Lower))

I may understand more clear if you can upload a sample file with wanted result.

Regards.

**aravindhan_31** · 11-24-2019, 12:45 PM

Hi,

Thanks for the response, the formula is not working as per my requirement.

below is the screen shot

.Capture.PNG

what i am looking for is, I work only in the shift of 12:30 PM to 21:30 PM, any tickets that are raised I am supposed to solve only during my shift. in 3rd record, though the request was raised at 8:15 am in the morning
I assume its raised at 12:30 PM and then i solved at 15:05 which gives 2hours 35 minutes, however the result is wrong for 1st record, 2nd record etc.

Thanks for your help

Regards
Arvind

**Roel Jongman** · 11-24-2019, 01:49 PM

Please Post the example file and not a screenshot.. that will make it a lot easier for us to help you.

Instructions on how to post are in the Yellow banner at the top of your screen

**Pepe Le Mokko** · 11-24-2019, 02:05 PM

After 180+ posts you should know that attaching a sheet makes life easier and gets you faster answers .

**menem** · 11-24-2019, 09:40 PM

I've couple of question to ask before continue,

You said that it's your shift time , that's mean there are others shift time right ?
And how to handle (or count) shift that cross weekend or holidays over mid-night?
(Continue to count until end to next SAT?/SUN?/Holiday? , or , stop at 00:00 )
[It's may need to prepare on the first hand, in case your time shift changed or
want to implement to use with others. ]

On each request only shift owner will be solved , others can not (or will not) solved it ?

Regards.

**menem** · 11-25-2019, 05:45 AM

In this file there are some agreement to complied.

1. Only one report sheet.
2. Multiple shift sheet is possible.
3. Stop date/time MUST greater than start date/time.
4. Amount of rows in shift sheet must greater than
(first date - last date) * 24 rows + 10 .
(ie. if calculate = 100 , it should be 110 rows)
5. Helper columns is needed.

Concept (or calculation process).

1. Prepare helper sheet for calculate count/not count time.
2. Seek for proper start / stop time.
3. Adjust gap between proper start and start , proper stop and stop.

And since web site not allow me to post formulas (It say something about web html etc.)
So, please review in the file.

Regards.

**aravindhan_31** · 11-27-2019, 02:26 AM

My bad,

please accept my sincere apologies. I should have that done that. just slipped out of my mind.

Regards
Arvind

**aravindhan_31** · 11-27-2019, 03:14 AM

Thanks Menem, need a little modification for minutes.

just to simplify, can we just look at Number of hours and minutes between Issue raised vs resolved irrespective of weekends, shifts or holidays?

Regards
Arvind

**menem** · 11-27-2019, 04:49 AM

In case you want to count for all days.
Please try this formula.

C2

Please Login or Register  to view this content.

A2 = Start (Request) date/time
B2 = Stop (Solved) date/time
G1 : Start Shift = 21:30
G2 : Stop Shift = 12:30
G3 : Time Gap =

Please Login or Register  to view this content.

Note : It's look like a SLA count ?

Regards.

**aravindhan_31** · 12-01-2019, 10:29 AM

Hi,

This worked.. thank you so much for the help

**aravindhan_31** · 12-02-2019, 04:41 AM

Hi,
sorry for re-opening this, for the actual data its not working. Attached the sample file. When request time and solved date are in same day, then it works, if different dates its not working.

**davsth** · 12-02-2019, 05:38 AM

You need to give your expected answers, rather than say it is not working. It is not helpful for people wishing to assist you

**aravindhan_31** · 12-03-2019, 04:51 AM

HI,

added a sample file again with desired result for 3 rows.

bascially what i am looking for:

I work in a support team, users/customers can raise ticket any time, but i will solve only during my shift timings which 12:30 to 21:30 & i wanted to know how much of time that i taken to solve the issue.

if someone raise ticket at 10:30 and i solved at 13:30, this duration might show 3 hours, but actually i solved within 1 hour from during my shift,
similarly if i solve the next day, then 9 hours of day 1 + 1 hour of next day which will be 10 hours that i have taken to solve this.

hope i explained my requirement clearly this time

Regards
Arvind

**JeteMc** · 12-05-2019, 07:46 PM

This proposal employs three helper columns (I:K) which may be moved and/or hidden for aesthetic purposes.
Column I covers tickets resolved in one day using: =IF(MOD(B3,1)<C3,"",IF(INT(A3)=INT(B3),MIN(D3,MOD(B3,1))-MAX(C3,MOD(A3,1)),""))
Column J covers tickets resolved in two days and partially covers tickets resolved over more than two days, using: =IF(INT(B3)>INT(A3),SUM(MAX(0,D3-MAX(C3,MOD(A3,1))),MAX(0,MIN(D3,MOD(B3,1))-C3)),"")
Column K covers the remainder of tickets that take more than two days to resolve using: =IF(NETWORKDAYS(A3,B3)>2,(NETWORKDAYS(A3,B3)-2)*TIME(9,0,0),"")
Column G may then be populated using: =SUM(I3:K3)
Note that the formula for column G is shown in column L in the attached file and agrees with 2 of the 3 desired results. I don't understand how G3 could be 9 and G4 be 9:15 instead of 10:15.
Let us know if you have any questions.

**menem** · 12-06-2019, 01:09 PM

Please test in this file (I've back to my original file +option to count on weekend)
But 2nd row , my file got 10:15 hours not 9:15 hours.

And sorry for reply late.

Regards.

**aravindhan_31** · 12-18-2019, 01:39 PM

This worked thanks JeteMc and rest as well...

**JeteMc** · 12-18-2019, 04:55 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Calculate number of hours and minutes between 2 date and time

LinkBack

Thread Tools

Rate This Thread

Display

Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Re: Calculate number of hours and minutes between 2 date and time

Thread Information

Users Browsing this Thread

Similar Threads

Calculate the number of days, hours, minutes and second between two dates

[SOLVED] Calculate number of hours from Start date and time and End date and time

Adding days hours and minutes to calculate time of end of auction?

Calculate minutes into hours in number format

How do I calculate the number of days hours minutes and seconds between 2 date & time

How do I calculate the number of days hours minutes and seconds between 2 date & time

calculate number of hours between two date/time combinations

Bookmarks

Bookmarks

Posting Permissions