+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    05-10-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

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

    Use

    =MOD(A1-A2/24,1)

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    05-10-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by newdoverman; 11-07-2014 at 09:04 PM.

  6. #6
    Registered User
    Join Date
    05-10-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #7
    Registered User
    Join Date
    05-10-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #8
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    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: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-10-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    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. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel Formula to Show Type of Shift Based on Start and End Time
    By bjnockle in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 07-03-2013, 09:48 PM
  2. Calculate finish time given start time, working hours and job duration
    By swanseaexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2012, 03:00 PM
  3. Calculating END time based on start time, breaks, and hours to complete
    By sweedey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2012, 11:29 AM
  4. Replies: 5
    Last Post: 02-19-2009, 01:41 PM
  5. Replies: 1
    Last Post: 03-27-2006, 01:10 PM

Bookmarks

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