CODEWITHSUNDEEP
exceltimeschedule
A. Naik
A. Naik

Reputation: 1

Employee schedule - Calculate total hours from time in 1 cell; auto format time

I have an existing employee schedule excel file to which I'd like to make "smarter."

For each employee, time is entered in one cell (e.g. 7AM-3PM) for each day of the week. I would like to have the Total Hours for each employee calculated at the end of the week. I have a formula that does that, but it brings up additional problems. 

    =((SUBSTITUTE(B4,LEFT(B4,FIND("-",B4)+1),"")*1-SUBSTITUTE(B4,RIGHT(B4,FIND("-",B4)-1),"")*1)+(SUBSTITUTE(B4,LEFT(B4,FIND("-",B4)+1),"")*1<SUBSTITUTE(B4,RIGHT(B4,FIND("-",B4)-1),"")*1))*24

Here, the time is entered in cell B4 and this formula is pasted in the cell that you would like to have calculate the total hours.

In this formula, the time needs to be entered in the format "XX:XX AM - XX:XX PM" otherwise it doesn't work. Also if a cell is blank or says OFF, it again doesn't work. So what I would like is to automatically convert times entered in any cell as "7AM-3PM" to the format "7:00 AM - 3:00 PM."

Also, if a cell value is blank or says OFF, for it to consider the value of the cell to be "00:00 AM - 00:00 AM" but still be displayed as blank/OFF.

The way I am calculating the total hours for the week is summing up the formula above for each day (it becomes very long and I'm not sure if this is the best way to do it).

Any help for this would be appreciated.

Upvotes: 0

Views: 546

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

This will take your entry and do what you want:

=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,LEN(A1)),"AM"," AM"),"PM"," PM")) -TRIM( SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("-",A1)-1),"AM"," AM"),"PM"," PM")),0)

[1]: https://i.sstatic.net/A3i

Upvotes: 1

Related Questions