Microsoft Excel - time calculaion in excel - Asked By anu anu on 11-Mar-14 02:48 AM

hi ,

i need one excel formula for time calculation acording to service window and working Hrs.i have data with me like below

Ticket Date	Arival Time	Call Closing Date	Office Start Time	Office End Time	Service Window	Response Time	Down Time
25/10/2025 13:40	26/10/2025 15:35	26/10/2025 16:50	9:00:00	18:00:00	Mon to Sat	b2-a2	c2-a2
26/10/2025 17:01	28/10/2025 10:55	28/10/2025 15:35	9:00:00	17:30:00	Mon to Fri
28/10/2025 14:03	30/10/2025 10:40	30/10/2025 11:30	9:00:00	17:30:00	Mon to Fri
08/10/2025 10:40	10/10/2025 14:30:00	10/10/2025 15:00	9:00:00	18:00:00	Mon to Sat
22/10/2025 10:09	10/23/2013 17:45:00	23/10/2025 19:05	9:00:00	17:30:00	Mon to Fri
22/10/2025 10:17	10/22/2013 19:00:00	22/10/2025 20:00	9:00:00	17:30:00	Mon to Fri
17/10/2025 11:44	10/22/2013 10:00:00	22/10/2025 15:10	9:00:00	17:30:00	Mon to Fri
21/10/2025 10:44	10/21/2013 17:20:00	21/10/2025 17:55	9:00:00	17:30:00	Mon to Fri
21/10/2025 17:40	10/23/2013 11:55:00	23/10/2025 12:50	9:00:00	17:30:00	Mon to Fri
21/10/2025 17:41	10/23/2013 10:00:00	23/10/2025 10:40	9:00:00	17:30:00	Mon to Fri

Thanks
Anu

Hello Anu,
This is very similar to this.

https://www.nullskull.com/q/10473451/excel-time-calculation.aspx

You will end up with very complex formulae and it might be better to consider writing a VBA function that can be used in your spreadsheet to do the calculation.
Regards
Harry

Usha,
Can you indicate what the answers to the various scenarios that you give are?  That is fill in the Response Time and Down Time columns.
Regards
Harry

hi harry,

can you help me out writing a VBA function for this file.

Thanks
anu

Quite possibly if I know what answers you want.
Harry

hi harry .

like i have Ticket Date date and time in column A And Arival Time Column B, Closing Date in Column C.

Now i Need Response Time in column D And Down Time in Column E .

calculation For Repose time Arival - Ticket DAte (working hours 9:00 to 17:30 MOnday to Friday )

calculation For Repose time Closing Date  - Ticket DAte (working hours 9:00 to 17:30 MOnday to Friday )

Ticket Date	Engineer Arival Time	Call Closing Date	Response Time	Down Time
10/25/2013 13:40	10/26/2013 15:35	10/26/2013 16:50
10/26/2013 17:01	10/28/2013 10:55	10/28/2013 15:35
10/28/2013 14:03	10/30/2013 10:40	10/30/2013 11:30
10/08/2025 10:40	10/10/2025 14:30	10/10/2025 15:00
10/22/2013 10:09	10/23/2013 17:45	10/23/2013 19:05
10/22/2013 10:17	10/22/2013 19:00	10/22/2013 20:00
10/17/2013 11:44	10/22/2013 10:00	10/22/2013 15:10
10/21/2013 10:44	10/21/2013 17:20	10/21/2013 17:55
10/21/2013 17:40	10/23/2013 11:55	10/23/2013 12:50
10/21/2013 17:41	10/23/2013 10:00	10/23/2013 10:40
10/17/2013 14:30	10/19/2013 08:10	10/19/2013 10:20
10/23/2013 10:18	10/24/2013 10:50	10/24/2013 12:50
10/16/2013 10:08	10/19/2013 13:50	10/19/2013 17:00
10/23/2013 14:01	10/25/2013 10:30	10/25/2013 11:50
10/15/2013 20:14	10/16/2013 11:35	10/16/2013 14:35

Thanks
Anu

Anu,
I thought I knew what you wanted from the table that you presented the first time and now it is different.  Also now the working week and the opening hours are the same where they varied before.  What is the real situation?
Also what I wanted you to do was to enter the answers (hours and minutes presumably) that are required in the response time and down time columns so that I could check that whatever I might develop was giving the right answer so that I did not give you something that gives the incorrect answer.
Harry

Hi Anu,
This file contains as far as I got before my last request for more detail.  Probably doesn't give the answers you want in all cases.
anu_times_1.zip
Regards
Harry