Hello,
I need to automate a process for calculating monthly time city-wise. Here is the process description:
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, so 09:00 to 09:30 = 00:30).
For Tokyo, it would be 3 hours 30 minutes (since office stop time is 6 pm, so 14:30 to 18:00 = 03:30).
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 throughout the month, at varying times, with varying participation of cities. At the end of the month, I need to calculate the total time per city, for each 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.
I have also attached a screenshot, in case you'd rather not download a file. Here it is: timecalc.png
Would greatly appreciate your help in automating this calculation using formulae/VBA/macros in Excel.
Thanks and regards,
Bookmarks