+ Reply to Thread
Results 1 to 4 of 4

Converting Time Frame based on Cell Values

  1. #1
    Registered User
    Join Date
    09-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    10

    Converting Time Frame based on Cell Values

    Guru's

    I've looked all over the place with no luck.

    Is there a function to change time frame based on cell value.

    For example, in column D i have a list of date and times values in Central time and in column E i have values of "Central" "Pacific" or "Eastern".

    Is there a way to change time based on values and place results on column G?

    Attachment is an idea on what i would like to have.

    Any ideas will be appreciated.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Converting Time Frame based on Cell Values

    hi reyrey. 2 ways here. 1 is to put the time differences in a table somewhere in say J4:K6
    Eastern -1
    Central 0
    Pacific -2
    then do a VLOOKUP like this:
    =D4+VLOOKUP(E4,$J$4:$K$6,2,0)/24

    the other way is to exclude the table & do multiple IFs:
    =D4-IF(E4="Eastern ",1/24,IF(E4="Central ",0,2/24))
    notice my formula for the Time Zone has a space. i followed yours. your Column E texts has a trailing space
    Last edited by benishiryo; 08-06-2013 at 08:58 PM.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Converting Time Frame based on Cell Values

    Pacific time to Central time should be +2 instead of -2 from your example

  4. #4
    Registered User
    Join Date
    09-20-2011
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Converting Time Frame based on Cell Values

    Works like a charm!!!

    Thanks for the quick response!!!

+ 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. Replies: 1
    Last Post: 12-27-2012, 02:43 PM
  2. Creating a Formula based on time frame in a drop down box
    By Caileanbw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2012, 02:18 AM
  3. Loan Calculator with Terms based on Fixed Payment not a fixed time frame.
    By cc4digital in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2012, 04:49 AM
  4. Excel 2007 : Adds values for a specific time frame
    By Raist23 in forum Excel General
    Replies: 3
    Last Post: 03-15-2012, 05:43 AM
  5. spreading values across a time frame
    By REDeveloper in forum Excel General
    Replies: 6
    Last Post: 10-20-2009, 11:55 AM

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