Converting text to numbers

[JenniferMurphy]

I would like to find code that will generate the data in col C using the text data in A & B.

C/R	A	B	C
3	Beg	End	Hours
4	Fri 7:00 pm	Fri 7:00 pm	0.00
5	Fri 7:00 pm	Fri 9:00 pm	2.00
6	Fri 7:00 pm	Sat 7:00 am	12.00
7	Mon 3:15 pm	Mon 7:45 pm	4.50
8	Fri 7:00 pm	Sat 7:00 pm	24.00
9	Fri 7:00 pm	Sun 7:00 pm	48.00
10	Fri 7:00 pm	Mon 7:00 pm	72.00
11	Fri 7:00 pm	Tue 7:00 pm	96.00
12	Fri 7:00 pm	Wed 7:00 pm	120.00
13	Fri 7:00 pm	Thu 7:00 pm	144.00
14	Fri 7:00 pm	Fri 7:00 pm	168.00

<tbody>
</tbody>

I think it will have to be a UDF. What's the best way to convert the days (Sun, Mon, ...) into numbers (1, 2, ...)?

 

[hoanganhdl]

C4=B4-A4
Format cell column C is Custom [hh]:mm

 

[Rick Rothstein]

I would like to find code that will generate the data in col C using the text data in A & B.

C/R	A	B	C
3	Beg	End	Hours
4	Fri 7:00 pm	Fri 7:00 pm	0.00
5	Fri 7:00 pm	Fri 9:00 pm	2.00
6	Fri 7:00 pm	Sat 7:00 am	12.00
7	Mon 3:15 pm	Mon 7:45 pm	4.50
8	Fri 7:00 pm	Sat 7:00 pm	24.00
9	Fri 7:00 pm	Sun 7:00 pm	48.00
10	Fri 7:00 pm	Mon 7:00 pm	72.00
11	Fri 7:00 pm	Tue 7:00 pm	96.00
12	Fri 7:00 pm	Wed 7:00 pm	120.00
13	Fri 7:00 pm	Thu 7:00 pm	144.00
14	Fri 7:00 pm	Fri 7:00 pm	168.00

<tbody>
</tbody>

I think it will have to be a UDF. What's the best way to convert the days (Sun, Mon, ...) into numbers (1, 2, ...)?

Are these times tied to real calendar dates in anyway? If so, can you tell us how they are linked to such ral dates? I ask because you times values can span multiple days (as C/R 10 shows) which means that we would know if any of the times crossed from February into March and whether it was a Leap Year or not.

 

[tygrrboi]

I only tested this with your actual example data... so I am not sure if it works all the time... but it sure was fun!

Give this a try:


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #33af4a}span.s5 {color: #0057d6}</style>=24*(MOD(7+(MATCH(LEFT(B2,3), {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}, 0)-MATCH(LEFT(A2,3), {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}, 0)),7)-(RIGHT(B2,2)="am"))+ABS((60*((RIGHT(B2,2)="pm")*12+MID(B2,FIND(" ",B2)+1,FIND(":",B2)-FIND(" ",B2)-1))+MID(B2,FIND(":",B2)+1,2))-(60*((RIGHT(A2,2)="pm")*12+MID(A2,FIND(" ",A2)+1,FIND(":",A2)-FIND(" ",A2)-1))+MID(A2,FIND(":",A2)+1,2)))/60

 

[JenniferMurphy]

C4=B4-A4
Format cell column C is Custom [hh]:mm

The data in A & B is text, literal text. The data in A4 is "Fri 7:00 pm". If I apply your formula, I get "VALUE!".

 

[JenniferMurphy]

Are these times tied to real calendar dates in anyway? If so, can you tell us how they are linked to such ral dates? I ask because you times values can span multiple days (as C/R 10 shows) which means that we would know if any of the times crossed from February into March and whether it was a Leap Year or not.

The times are not tied to any particular date. I should have been more explicit about that. They are all within a week of each other. I tried to illustrate that with the data rows 4 & 8-14. The End time (Col B) is the next occurrence of that time up to a week later.

I also see that there is an error in my table above. Rows 4 and 14 give different results for the same data. (sigh) Sorry about that.

Here's a better table.

C/R	A	B	C	D	E
3	Beg	End	Hours	Days	Comments
4	Fri 7:00 pm	Fri 9:00 pm	2.00	0.0833	From 7 pm to 9 pm on same day
5	Mon 3:15 pm	Mon 7:45 pm	4.50	0.1875	From 3:15 pm on Mon to 7:45 pm on the same day
6	Fri 7:00 pm	Sat 7:00 pm	24.00	1.0000	From 7 pm on Fri to 7 pm on Sat, the next day
7	Fri 7:00 pm	Sun 7:00 pm	48.00	2.0000	From 7 pm on Fri to 7 pm on Sun, 2 days later
8	Fri 7:00 pm	Mon 7:00 pm	72.00	3.0000	From 7 pm on Fri to 7 pm on Mon, 3 days later
9	Fri 7:00 pm	Tue 7:00 pm	96.00	4.0000	From 7 pm on Fri to 7 pm on Tue, 4 days later
10	Fri 7:00 pm	Wed 7:00 pm	120.00	5.0000	From 7 pm on Fri to 7 pm on Wed, 5 days later
11	Fri 7:00 pm	Thu 7:00 pm	144.00	6.0000	From 7 pm on Fri to 7 pm on Thu, 6 days later
12	Fri 7:00 pm	Fri 7:00 pm	168.00	7.0000	From 7 pm on Fri to 7 pm on Fri, 7 days later
13	Fri 7:00 pm	Fri 6:00 pm	167.00	6.9583	From 7 pm on Fri to 6 pm on Fri, 7 days later

<tbody>
</tbody>

 

[JenniferMurphy]

I only tested this with your actual example data... so I am not sure if it works all the time... but it sure was fun!

Give this a try:


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #33af4a}span.s5 {color: #0057d6}</style>=24*(MOD(7+(MATCH(LEFT(B2,3), {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}, 0)-MATCH(LEFT(A2,3), {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}, 0)),7)-(RIGHT(B2,2)="am"))+ABS((60*((RIGHT(B2,2)="pm")*12+MID(B2,FIND(" ",B2)+1,FIND(":",B2)-FIND(" ",B2)-1))+MID(B2,FIND(":",B2)+1,2))-(60*((RIGHT(A2,2)="pm")*12+MID(A2,FIND(" ",A2)+1,FIND(":",A2)-FIND(" ",A2)-1))+MID(A2,FIND(":",A2)+1,2)))/60

Wow! I am impressed, but I worry about your definition of "fun".

I couldn't get it to work. If I just paste it into a cell, I get "#N/A". If I paste it into the formula bar and use Ctrl+Shift+Enter, I just get the literal formula.

What are the {}"? Is this an array formula?

 

[Rick Rothstein]

The times are not tied to any particular date. I should have been more explicit about that. They are all within a week of each other. I tried to illustrate that with the data rows 4 & 8-14. The End time (Col B) is the next occurrence of that time up to a week later.

I still think you could be off by 24 hours if the two times cross from February to March in a Leap Year, but since that does not appear concern you, give this formula for hours a try...

=24*(MOD((FIND(LEFT(B4,2),"SuMoTuWeThFrSa")-FIND(LEFT(A4,2),"SuMoTuWeThFrSa"))/2,7)+7*(AND(LEFT(A4,3)=LEFT(B4,3),MID(A4,LEN(A4)-6,8)>=MID(B4,LEN(B4)-6,8)))+TIMEVALUE((RIGHT(B4,8)))-TIMEVALUE(RIGHT(A4,8)))

 

[Eric W]

Something like this maybe?

	A	B	C	D
1	C/R	A	B
2	3	Beg	End	Hours
3	4	Fri 7:00 pm	Fri 7:00 pm	0.00
4	5	Fri 7:00 pm	Fri 9:00 pm	2.00
5	6	Fri 7:00 pm	Sat 7:00 am	12.00
6	7	Mon 3:15 pm	Mon 7:45 pm	4.50
7	8	Fri 7:00 pm	Sat 7:00 pm	24.00
8	9	Fri 7:00 pm	Sun 7:00 pm	48.00
9	10	Fri 7:00 pm	Mon 7:00 pm	72.00
10	11	Fri 7:00 pm	Tue 7:00 pm	96.00
11	12	Fri 7:00 pm	Wed 7:00 pm	120.00
12	13	Fri 7:00 pm	Thu 7:00 pm	144.00
13	14	Fri 7:00 pm	Fri 6:00 pm	167.00

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula D3 =MOD((MOD(MATCH(LEFT(C3,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)-MATCH(LEFT(B3,3),{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0),7)+MID(LEFT(C3,LEN(C3)-3),5,99)+IF(RIGHT(C3,2)="pm",0.5,0)-(MID(LEFT(B3,LEN(B3)-3),5,99)+IF(RIGHT(B3,2)="pm",0.5,0)))*24,168) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[JenniferMurphy]

C4=B4-A4
Format cell column C is Custom [hh]:mm

I was curious about your "[hh]:mm" format string. I could not find any description of this anywhere. It appears that it stops Excel from truncating the hours at "23". Is that correct? Here's my data formatted a couple of ways.

C/R	C	D	E	F	G	H
3	Beg	End	Hours	Days	h:mm	[h]:mm
4	Fri 01/01/16 07:00 PM	Fri 01/01/16 09:00 PM	2.00	0.0833	2:00	2:00
5	Mon 01/04/16 03:15 PM	Mon 01/04/16 07:45 PM	4.50	0.1875	4:30	4:30
6	Fri 01/01/16 07:00 PM	Sat 01/02/16 07:00 PM	24.00	1.0000	0:00	24:00
7	Fri 01/01/16 07:00 PM	Sun 01/03/16 07:00 PM	48.00	2.0000	0:00	48:00
8	Fri 01/01/16 07:00 PM	Mon 01/04/16 07:00 PM	72.00	3.0000	0:00	72:00
9	Fri 01/01/16 07:00 PM	Tue 01/05/16 07:00 PM	96.00	4.0000	0:00	96:00
10	Fri 01/01/16 07:00 PM	Wed 01/06/16 07:00 PM	120.00	5.0000	0:00	120:00
11	Fri 01/01/16 07:00 PM	Thu 01/07/16 07:00 PM	144.00	6.0000	0:00	144:00
12	Fri 01/01/16 07:00 PM	Fri 01/08/16 07:00 PM	168.00	7.0000	0:00	168:00
13	Fri 01/01/16 07:00 PM	Fri 01/08/16 06:00 PM	167.00	6.9583	23:00	167:00

<tbody>
</tbody>

Is this documented anywhere? Or is this one of the many typical M$FT stealth features?