outlandish
New Member
- Joined
- Jan 9, 2017
- Messages
- 1
Hello
I have a schedule template using the following format for hours:
<colgroup><col span="4"></colgroup><tbody>
</tbody>
what I need to do is for excel to calculate the hour value of each shift.
For example 2p-10:15p is 8.25 hours .
This is what I currently have but it is UGLY.
=IF(OR(B19="REQ*",B19="REQ"),"blank",TIME(MID(B19,FIND("-",B19)+1,LEN(B19)-FIND("-",B19)-2)+IF(RIGHT(B19,3)="p*",12,12),0,0)-TIME(LEFT(B19,FIND("-",B19)-2)+IF(MID(B19,FIND("-",B19)-1,2)="pm",12,0),0,0))
Please advise
Thank you
I have a schedule template using the following format for hours:
6:30a-2p* | OFF | REQ* | 7a-3:30p* |
2p-10:15p* | 2p-10:15p* | 2p-10:15p* | OFF |
REQ | 5a-11a* | 5a-11a* | OFF |
<colgroup><col span="4"></colgroup><tbody>
</tbody>
what I need to do is for excel to calculate the hour value of each shift.
For example 2p-10:15p is 8.25 hours .
This is what I currently have but it is UGLY.
=IF(OR(B19="REQ*",B19="REQ"),"blank",TIME(MID(B19,FIND("-",B19)+1,LEN(B19)-FIND("-",B19)-2)+IF(RIGHT(B19,3)="p*",12,12),0,0)-TIME(LEFT(B19,FIND("-",B19)-2)+IF(MID(B19,FIND("-",B19)-1,2)="pm",12,0),0,0))
Please advise
Thank you