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

1. ## 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

2. ## 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?

3. ## 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. ## 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. ## 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.

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