Multi timezone calculator

[Prometheus2015]

Hello,
This is my first forum post on the Internet. I guess I should start with defining the manual process that I think Excel can automate.

There are offices in multiple cities, all working 9am to 6pm in their respective time zones. There are processes that are run at the Head Office that works on India standard time. Selected offices across the world will run that process at the same time, if they are working. For example: A process is started at 11:00 hours IST and runs till 16:00 hours IST. The Selected cities that will participate are say, London and Tokyo. In London, the start time would therefore be 05:30 GMT and end time would be 09:30 GMT. Similarly, the start time for Tokyo would be 14:30 Japan time and end would be 19:30 Japan time. Now, let's see how many working hours for London and Tokyo respective is that:
For London, it would be 30 minutes (since office start time is 9 am).
For Tokyo, it would be 3 hours 30 minutes (since office stop time is 6 pm).
For Delhi (India), it would be the full 4 hours since both process start time and process end time are within working hours.

Such processes would occur every few days through the month. At the end of the month, I need to calculate the total time per city.

I have manually created an Excel sheet that can give you a visual of the data available and the output required. I've shared the file on http://jmp.sh/EDOZ2ES.

Would greatly appreciate your help in automating this calculation using formulae/VBA/macros in Excel.

Thanks and regards,
Sagar

 

[Prometheus2015]

Lords of Excel!
A little help please?

 

[Michael M]

I, and many other posters, won't download a workbook / sheet, for obvious reasons.
While I can't see the process, would this link help get you started ??

http://www.mrexcel.com/forum/excel-questions/253199-world-clock-excel.html

 

[Prometheus2015]

Hi Michael,
Thanks for the response. I went through the world clock you shared, but it doesn't meet my requirement in entirety. I understand downloading a workbook might not be wise. I've therefore taken a screenshot of the workbook and put it here:

Hope this image, and the text in my original post, describes the problem better.

Would greatly appreciate your help.

Thanks and regards,
Sagar.

 

[Prometheus2015]

Oh wonderful! The image won't show. Any ideas how I could share a safe .png file to show the input/process/output expected?

 

[Prometheus2015]

B.U.M.P.

 

[Michael M]

I'd suggest then, using the HTML Maker in my tag to post a SMALL sample of what is required back here !

 

[Prometheus2015]

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		City Name	Time Difference	Working Hours		Process	Start Time MLT	End Time MLT	Cities Included		Process	City	Affected Hours		Citywise Total Affected Hours
3		City 1	MLT - 4	09:00 to 18:00		Process 1	01-01-2015 11:20	01-01-2015 15:38	City 1, City 3, City 9		Process 1	City 1	02:38		City 1	06:55
4		City 2	MLT - 3.30	09:00 to 18:00		Process 2	10-02-2015 21:00	11-02-2015 05:15	City 1, City 10			City 3	03:08		City 2	00:00
5		City 3	MLT - 3.30	09:00 to 18:00		Process 3	07-03-2015 14:40	07-03-2015 17:57	City 1, City 5, City 9			City 9	04:10		City 3	03:08
6		City 4	MLT - 3.30	09:00 to 18:00							Process 2	City 1	01:00		City 4	00:00
7		City 5	MLT- 2.30	09:00 to 18:00								City 10	00:15		City 5	03:17
8		City 6	MLT	09:00 to 18:00							Process 3	City 1	03:17		City 6	00:00
9		City 7	MLT	09:00 to 18:00								City 5	03:17		City 7	00:00
10		City 8	MLT + 2.30	09:00 to 18:00								City 9	00:50		City 8	00:00
11		City 9	MLT + 2.30	09:00 to 18:00											City 9	05:00
12		City 10	MLT + 4	09:00 to 18:00											City 10	00:00
13
14			MLT = My Local Time
15								One Time Master Data
16								Daily Data Entry
17								Monthly Output
18
Sheet1