# Subtracting hours from a shift start time to show time in other time zones

1. ## Subtracting hours from a shift start time to show time in other time zones

I am trying to subtract hours from a time to show the time it is in different time zones depending on where the client is located. I have a team that work in India, but support systems in the USA. I am trying to use an if and match clause so that the time zone can be chosen and depending on the time of year, it will show the correct time. In very simple terms, I have a cell with a time in it: 1:30 AM. I have another cell with the number of hours difference. For example, it is 13.5 hours earlier in the Pacific time zone so that cell says 13.5

I can not subtract 13.5 hours from 1:30 am. I thought the simple formula was a1-a2/24 but that does not come up with the correct time either.

Any Ideas?

2. ## Re: Subtracting hours from a shift start time to show time in other time zones

Use

=MOD(A1-A2/24,1)

3. ## Re: Subtracting hours from a shift start time to show time in other time zones

If you are dealing with the International Date Line (different day as well as time) then I would enter both the Date and Time in say A2 and the time difference in B2 then in C2 =A2-B2

Format A2 and C2 as Date and time and B2 as hours and minutes.

If the International Date Line never comes into play and date confusion isn't an issue then the formula =MOD(A1-A2/24,1) previously given by Bob Phillips will work just fine.

4. ## Re: Subtracting hours from a shift start time to show time in other time zones

Wow. That absolutely works. I was hoping whatever simple formula I received would work in my if statement. Here is my statement:

=IF(\$E\$2="Yes",SUM(C6+(INDEX('Time Calculator'!\$D\$3:\$D\$8/24,1,MATCH(B\$5,'Time Calculator'!\$A\$3:\$A\$8,0)))),IF(\$E\$2="No",SUM(C6+(INDEX('Time Calculator'!\$D\$10:\$D\$13/24,1,MATCH(B\$5,'Time Calculator'!\$A\$10:\$A\$13,0))))))

On the first page, in cell E2, there is an entry for YES if it is Standard Time or NO if it is Daylight Savings Time. You then choose the time zone (Pacific, Mountain, Central or Eastern). I then enter the shift start time in IST (cell C6). On the Time Calculator Page, I list the time zones and the total hours they are less than IST. If I list them in negative numbers or try and subtract C6 above, I get a bunch of endless ### signs. If I leave it as entered above I get a result, it is just wrong!

I tried to replace the MOD above and it said I had too few arguments and I could not figure out how to get it to work in my formula string. Any suggestions would be appreciated. I just play around till I google something or find something that works so perhaps I am simply going about this incorrectly!

I am attaching my workbook just in case it is helpful! It is on the second and third tabs.

Thank you!
Nancie
My Teams (2014-11-01) v43.xlsx

5. ## Re: Subtracting hours from a shift start time to show time in other time zones

I think that I see something not quite right here. A start time of 1:30 AM IST should indeed convert to 12:00 PM PST according to timeanddate.com The mod formula is correct as shown. E6 and F6 have incorrect times.

If I'm correct in my "thinking" LOL the formula in E6 should be as follows. Copy to F6 and down.
Formula:
`Please Login or Register  to view this content.`

6. ## Re: Subtracting hours from a shift start time to show time in other time zones

Thank you -- that does work fine. The only thing that I am not able to add into the IF statement is to MATCH the entry in C5 (Pacific, Mountain, Central and Eastern) so that it will choose the correct number of hours to subtract from the Time Calculator tab.

Does anyone know how/if I can use the MATCH command with MOD?

7. ## Re: Subtracting hours from a shift start time to show time in other time zones

I am so frustrated. I must be doing something wrong. If I enter this:

=IF(\$C6="","",IF(\$E\$2="Yes",MOD(C6-'Time Calculator'!\$D5:\$D8/24,1),MATCH(B\$5,'Time Calculator'!\$A\$5:\$A\$8,0)))

It calculates the correct time by timezone. However, when I attempt to add the IF "NO" match, it says I have too many arguments. I am sure it is something simple, I just can't see it!

=IF(\$C6="","",IF(\$E\$2="Yes",MOD(C6-'Time Calculator'!\$D5:\$D8/24,1),MATCH(B\$5,'Time Calculator'!\$A\$5:\$A\$8,0,IF(\$E\$2="No",MOD(C6-'Time Calculator'!\$D5:\$D8/24,1),MATCH(B\$5,'Time Calculator'!\$A\$5:\$A\$8,0))))

Thank you so much!
Nancie

8. ## Re: Subtracting hours from a shift start time to show time in other time zones

I 'm not sure of my understanding, may be try this

Formula:
`Please Login or Register  to view this content.`

9. ## Re: Subtracting hours from a shift start time to show time in other time zones

Here is one way of using the MOD function in your worksheet by using NAMED RANGES. The start and end times titles also reflect the choices made for time zone for Client 1. No other clients have been touched.

10. ## Re: Subtracting hours from a shift start time to show time in other time zones

Thank you Newdoverman!!! That works perfect!! I have never used Named Ranges before -- AWESOME!!

11. ## Re: Subtracting hours from a shift start time to show time in other time zones

Thank you for the feedback. Bob Phillips's was a good one to use with named ranges where the contents of two cells combine to make a name.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1