Dear Excel users,
Could you please help me with which formula can be used for either:

First column we key in "1st - 12th" and the next column will auto calculate
"12 days" x $284 (this amount is not fix so we need to manually key in) and
finally the last column will auto calculate "3408"

OR

First column we key in "person's name (1st-12th)", next column auto
calculate "12 days", next column we key in $284, last column auto calculate
"3408"

I hope you understand what i mean, and can assist me.
Thank you.
Regards,
Kelly

Do you mean:

A1: 1st - 12th
B1: =IF(ISERROR(FIND("1st - 12th",A1,1)0),"",MID(A1,FIND("th",A1,1)-2,2) &
" days")
C1: 284
D1: =IF(B1<"",C1*EVAL(LEFT(B1,FIND(" ",B1,1)-1)),0)

This currently only works if "Days" are =10 i.e two digits

What do want to happen if A1 does not equal "1st - 12th"? Above will set B1
to blank and give 0 in D1.

HTH

"Kelly Lim" wrote:

Dear Excel users,
Could you please help me with which formula can be used for either:

First column we key in "1st - 12th" and the next column will auto calculate
"12 days" x $284 (this amount is not fix so we need to manually key in) and
finally the last column will auto calculate "3408"

OR

First column we key in "person's name (1st-12th)", next column auto
calculate "12 days", next column we key in $284, last column auto calculate
"3408"

I hope you understand what i mean, and can assist me.
Thank you.
Regards,
Kelly

I'm sorry, for my misinterpretion.
It doesnt have to be fixed from the date of 1st - 12th, e.g. if i key in 5th
- 9th, it will auto calculate "5 days" or 22nd - 25th, it will auto calculate
"4 days".

Regards,
Kelly

"Toppers" wrote:

Do you mean:

A1: 1st - 12th
B1: =IF(ISERROR(FIND("1st - 12th",A1,1)0),"",MID(A1,FIND("th",A1,1)-2,2) &
" days")
C1: 284
D1: =IF(B1<"",C1*EVAL(LEFT(B1,FIND(" ",B1,1)-1)),0)

This currently only works if "Days" are =10 i.e two digits

What do want to happen if A1 does not equal "1st - 12th"? Above will set B1
to blank and give 0 in D1.

HTH

"Kelly Lim" wrote:

Dear Excel users,
Could you please help me with which formula can be used for either:

First column we key in "1st - 12th" and the next column will auto calculate
"12 days" x $284 (this amount is not fix so we need to manually key in) and
finally the last column will auto calculate "3408"

OR

First column we key in "person's name (1st-12th)", next column auto
calculate "12 days", next column we key in $284, last column auto calculate
"3408"

I hope you understand what i mean, and can assist me.
Thank you.
Regards,
Kelly

Kelly,
My fault - I realised after posting that you would want a general
solution...... but the approach in principle is correct?

"Kelly Lim" wrote:

I'm sorry, for my misinterpretion.
It doesnt have to be fixed from the date of 1st - 12th, e.g. if i key in 5th
- 9th, it will auto calculate "5 days" or 22nd - 25th, it will auto calculate
"4 days".

Regards,
Kelly

"Toppers" wrote:

Do you mean:

A1: 1st - 12th
B1: =IF(ISERROR(FIND("1st - 12th",A1,1)0),"",MID(A1,FIND("th",A1,1)-2,2) &
" days")
C1: 284
D1: =IF(B1<"",C1*EVAL(LEFT(B1,FIND(" ",B1,1)-1)),0)

This currently only works if "Days" are =10 i.e two digits

What do want to happen if A1 does not equal "1st - 12th"? Above will set B1
to blank and give 0 in D1.

HTH

"Kelly Lim" wrote:

Dear Excel users,
Could you please help me with which formula can be used for either:

First column we key in "1st - 12th" and the next column will auto calculate
"12 days" x $284 (this amount is not fix so we need to manually key in) and
finally the last column will auto calculate "3408"

OR

First column we key in "person's name (1st-12th)", next column auto
calculate "12 days", next column we key in $284, last column auto calculate
"3408"

I hope you understand what i mean, and can assist me.
Thank you.
Regards,
Kelly

Try this in B1 (no name):

=MID(SUBSTITUTE(A1," ",""),FIND("-",SUBSTITUTE(A1,"
",""),1)+1,LEN(SUBSTITUTE(A1," ",""))-2 -FIND("-",SUBSTITUTE(A1,"
",""),1))-LEFT(SUBSTITUTE(A1," ",""),FIND("-",SUBSTITUTE(A1," ",""),1)-3)+1

I haven't added the "days" suffix

then D1 becomes =B1*C1

HTH

"Toppers" wrote:

Kelly,
My fault - I realised after posting that you would want a general
solution...... but the approach in principle is correct?

"Kelly Lim" wrote:

I'm sorry, for my misinterpretion.
It doesnt have to be fixed from the date of 1st - 12th, e.g. if i key in 5th
- 9th, it will auto calculate "5 days" or 22nd - 25th, it will auto calculate
"4 days".

Regards,
Kelly

"Toppers" wrote:

Do you mean:

A1: 1st - 12th
B1: =IF(ISERROR(FIND("1st - 12th",A1,1)0),"",MID(A1,FIND("th",A1,1)-2,2) &
" days")
C1: 284
D1: =IF(B1<"",C1*EVAL(LEFT(B1,FIND(" ",B1,1)-1)),0)

This currently only works if "Days" are =10 i.e two digits

What do want to happen if A1 does not equal "1st - 12th"? Above will set B1
to blank and give 0 in D1.

HTH

"Kelly Lim" wrote:

Dear Excel users,
Could you please help me with which formula can be used for either:

First column we key in "1st - 12th" and the next column will auto calculate
"12 days" x $284 (this amount is not fix so we need to manually key in) and
finally the last column will auto calculate "3408"

OR

First column we key in "person's name (1st-12th)", next column auto
calculate "12 days", next column we key in $284, last column auto calculate
"3408"

I hope you understand what i mean, and can assist me.
Thank you.
Regards,
Kelly

Yes, it works very well this time, except i did mention about:
First column we key in "person's name (1st-12th)" right?

the formula you gave me can only insert 1st-12th, if i put in any "name"
beside this dates in the same column, B1 will turn #VALUE.

Does this means i cannot insert any "name" near this date in A1?

Best regards,
Kelly

"Toppers" wrote:

Try this in B1 (no name):

=MID(SUBSTITUTE(A1," ",""),FIND("-",SUBSTITUTE(A1,"
",""),1)+1,LEN(SUBSTITUTE(A1," ",""))-2 -FIND("-",SUBSTITUTE(A1,"
",""),1))-LEFT(SUBSTITUTE(A1," ",""),FIND("-",SUBSTITUTE(A1," ",""),1)-3)+1

I haven't added the "days" suffix

then D1 becomes =B1*C1

HTH

"Toppers" wrote:

Kelly,
My fault - I realised after posting that you would want a general
solution...... but the approach in principle is correct?

"Kelly Lim" wrote:

I'm sorry, for my misinterpretion.
It doesnt have to be fixed from the date of 1st - 12th, e.g. if i key in 5th
- 9th, it will auto calculate "5 days" or 22nd - 25th, it will auto calculate
"4 days".

Regards,
Kelly

"Toppers" wrote:

Do you mean:

A1: 1st - 12th
B1: =IF(ISERROR(FIND("1st - 12th",A1,1)0),"",MID(A1,FIND("th",A1,1)-2,2) &
" days")
C1: 284
D1: =IF(B1<"",C1*EVAL(LEFT(B1,FIND(" ",B1,1)-1)),0)

This currently only works if "Days" are =10 i.e two digits

What do want to happen if A1 does not equal "1st - 12th"? Above will set B1
to blank and give 0 in D1.

HTH

"Kelly Lim" wrote:

Dear Excel users,
Could you please help me with which formula can be used for either:

First column we key in "1st - 12th" and the next column will auto calculate
"12 days" x $284 (this amount is not fix so we need to manually key in) and
finally the last column will auto calculate "3408"

OR

First column we key in "person's name (1st-12th)", next column auto
calculate "12 days", next column we key in $284, last column auto calculate
"3408"

I hope you understand what i mean, and can assist me.
Thank you.
Regards,
Kelly