+ Reply to Thread
Results 1 to 5 of 5

Converting text to time (HH:MM) for calculating hours worked

  1. #1
    Registered User
    Join Date
    01-11-2017
    Location
    Cleveland, OH USA
    MS-Off Ver
    Microsoft Office for Mac 15.30
    Posts
    2

    Question Converting text to time (HH:MM) for calculating hours worked

    Hi everyone,

    I am creating an employee schedule that uses a drop-down list for the manager to select IN and OUT times. The manager would like to use text for certain things like OPEN (9:30 AM) and CLOSE (10:30 / 11:00), and OFF ("blank" or 0:00) and ON CALL ("blank" or 0:00). These times need to be able to be calculated using the following formula:

    =IF(D8<C8,D8+1,D8)-C8

    where:
    • C8 = Start time
    • D8 = End time

    I have tried to use a very long IF formula to account for this; however, I get an error in the original IF formula at =IF(IF(D8="OPEN";"9:30"~)). This formula is below:

    =IF(IF(D8="OPEN";"9:30";IF(D8="CLOSE";"23:00";D8)) < IF(C8="OPEN";"9:30";IF(C8="CLOSE";"23:00";C8)); IF(D8="OPEN";"9:30";IF(D8="CLOSE";"23:00";D8))+"1:00"; IF(D8="OPEN";"9:30";IF(D8="CLOSE";"23:00";D8)))-C8

    I have tried to attach the file to the post. I appreciate any help that can be gained.

    RokOwen
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Converting text to time (HH:MM) for calculating hours worked

    The hours you have to select from are 9am-11:30pm. Why are you testing for OUT time being less than IN time? I don't see how the OUT time could be after midnight since those wee hours are missing from the list?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Converting text to time (HH:MM) for calculating hours worked

    So, assuming shifts really do occur within daytime hours, this is a better starting point:

    =IFERROR(INDEX({"23:00";"9:30"}, MATCH(D8,{"close";"open"},0)),D8) - IFERROR(INDEX({"23:00";"9:30"}, MATCH(C8,{"close";"open"},0)),C8)


    You "might" need to change those commas in the formula to semi-colons.

  4. #4
    Registered User
    Join Date
    01-11-2017
    Location
    Cleveland, OH USA
    MS-Off Ver
    Microsoft Office for Mac 15.30
    Posts
    2

    Re: Converting text to time (HH:MM) for calculating hours worked

    The equation tests for D8<C8 because it is using a 12-hour clock to do the calculations (I believe), and the result is a total number of hours worked.

    =IF(D8<C8,D8+1,D8)-C8

    where:
    • C8 = Start time
    • D8 = End time

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Converting text to time (HH:MM) for calculating hours worked

    The form you uploaded is not designed that way. It uses a drop down for entering times. So if the choice is 1pm it is correctly entered as 1pm. The formula I noted above thus is sufficient based on the presented design.

+ 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. Calculating staff roster time to hours, depending on time worked.
    By cookiet in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2013, 10:25 AM
  2. Cells not updating hours worked? Converting time and dollars?
    By xforum142riidax in forum Excel General
    Replies: 7
    Last Post: 09-11-2012, 04:38 PM
  3. Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  4. Calculating Hours worked and Time in Liu owed.
    By coccoster in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-24-2012, 04:46 AM
  5. Calculating basic hours worked, between a time range, problems
    By RoyLittle0 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-07-2012, 05:40 PM
  6. Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-07-2012, 06:59 AM
  7. Replies: 0
    Last Post: 01-05-2012, 06:23 AM
  8. Calculating Time and Half and Double Time (After 40 hours worked)
    By DoreenBassett in forum Word Formatting & General
    Replies: 1
    Last Post: 02-20-2009, 10:01 AM

Tags for this Thread

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