Splitting AM and PM hours from a Date Range

[mcmuney]

I'm working on scheduling and I have a need to split AM and PM hours from a date range.

For example, lets say someone works a shift between 6AM and 2:30PM. Currently, the cell shows "0600-1430". I imagine it would be difficult to calculate from this value. Assuming yes, I can split the value into two different cells, FROM being 0600 and TO being 1430.

Once the above is done, what's the simplest way to determine how many AM and PM hours? The answer should be 6 AM hours and 2.5 PM hours.

Thanks in advance.

 

[Phuoc]

Try this

Book1
	A	B	C
1	Data	From	to
2	0600-1430	6:00	14:30
Sheet1

Cell Formulas
Range	Formula
B2	B2	=--TEXT(LEFT($A2,4),"00\:00")
C2	C2	=--TEXT(RIGHT($A2,4),"00\:00")

 

[mcmuney]

Try this

Book1
	A	B	C
1	Data	From	to
2	0600-1430	6:00	14:30
Sheet1

Cell Formulas
Range	Formula
B2	B2	=--TEXT(LEFT($A2,4),"00\:00")
C2	C2	=--TEXT(RIGHT($A2,4),"00\:00")

Thank for that, but I'm actually trying to figure out how to go from here to calculating how many AM hours and PM hours there are in this time range.

The answer should be 6 AM hours and 2.5 PM hours. To be calculated from the time range.

Thanks. Let me know if I need to clarify further.

 

[Phuoc]

Like this?

Book1
	A	B	C
1	Data	AM	FM
2	0600-1430	6	2.5
3	0600-0730	1.5	0
4	1300-1530	0	2.5
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=MAX(0,MIN(12,TEXT(RIGHT($A2,4),"00\:00")*24)-TEXT(LEFT($A2,4),"00\:00")*24)
C2:C4	C2	=MAX(0,TEXT(RIGHT($A2,4),"00\:00")*24-MAX(12,TEXT(LEFT($A2,4),"00\:00")*24))

 

[mcmuney]

Like this?

Book1
	A	B	C
1	Data	AM	FM
2	0600-1430	6	2.5
3	0600-0730	1.5	0
4	1300-1530	0	2.5
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=MAX(0,MIN(12,TEXT(RIGHT($A2,4),"00\:00")*24)-TEXT(LEFT($A2,4),"00\:00")*24)
C2:C4	C2	=MAX(0,TEXT(RIGHT($A2,4),"00\:00")*24-MAX(12,TEXT(LEFT($A2,4),"00\:00")*24))

This is super! Thanks.

There's one scenario where it doesn't work: 2200-0630 (it shows 0 for both AM and PM)

Any reason for this?