any formula to auto calculate 1st-12th is 12 days pls?

K

Kelly Lim

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
 
T

Toppers

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
 
K

Kelly Lim

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
 
T

Toppers

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

Toppers

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
 
K

Kelly Lim

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
 
T

Toppers

YES! I did say in my reply "no name" [Try this in B1 (no name):]

If the "date" is in brackets, then I'll look to see if I can account for the
name (but it could be an even more complex formula):

e.g. David Jones (1st - 12th)

Is the above the format?

If I can't work out a formula, would a VBA solution be acceptable?
 
K

Kelly Lim

Apologies which i get your meaning wrong before.

it can be accepted e.g. (David Jones) 1st - 12th
but the formula do you mean to insert in B1
(no name):
as i cannot get the name out once i add in (no name): to the formula.

Kelly

Toppers said:
YES! I did say in my reply "no name" [Try this in B1 (no name):]

If the "date" is in brackets, then I'll look to see if I can account for the
name (but it could be an even more complex formula):

e.g. David Jones (1st - 12th)

Is the above the format?

If I can't work out a formula, would a VBA solution be acceptable?



Kelly Lim said:
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
Click to expand...
Click to expand...
 
T

Toppers

Kelly,
What I meant was that the formula would only work WITHOUT a name as
you have discovered. I don't think I could compose a formula whicch would
allow for a name; the experts might be able to.

And isn't just as easy to calculate the number of days manually (without any
formula) and enter this into column B? It must be quicker than typing in the
data in column A.

And doing it the way you are proposing means, for example, you cannot sort
the data by name if this is (was) required.

Apologies for the confusion.

Kelly Lim said:
Apologies which i get your meaning wrong before.

it can be accepted e.g. (David Jones) 1st - 12th
but the formula do you mean to insert in B1
(no name):
as i cannot get the name out once i add in (no name): to the formula.

Kelly

Toppers said:
YES! I did say in my reply "no name" [Try this in B1 (no name):]

If the "date" is in brackets, then I'll look to see if I can account for the
name (but it could be an even more complex formula):

e.g. David Jones (1st - 12th)

Is the above the format?

If I can't work out a formula, would a VBA solution be acceptable?



Kelly Lim said:
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

:

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

:

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

:

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

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
K

Kelly Lim

Thank you for the clarification. i understand by what you meant now.
If there's no formula for: e.g. David Jones (1st - 12th), then i'll try to
work out if having an extra column for the name itself would be ok or too
confusing.

VBA formula can do, provided if its not very confusing for me to get it
working.

Best regards,
Kelly

Toppers said:
Kelly,
What I meant was that the formula would only work WITHOUT a name as
you have discovered. I don't think I could compose a formula whicch would
allow for a name; the experts might be able to.

And isn't just as easy to calculate the number of days manually (without any
formula) and enter this into column B? It must be quicker than typing in the
data in column A.

And doing it the way you are proposing means, for example, you cannot sort
the data by name if this is (was) required.

Apologies for the confusion.

Kelly Lim said:
Apologies which i get your meaning wrong before.

it can be accepted e.g. (David Jones) 1st - 12th
but the formula do you mean to insert 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
Click to expand...

as i cannot get the name out once i add in (no name): to the formula.

Kelly

Toppers said:
YES! I did say in my reply "no name" [Try this in B1 (no name):]

If the "date" is in brackets, then I'll look to see if I can account for the
name (but it could be an even more complex formula):

e.g. David Jones (1st - 12th)

Is the above the format?

If I can't work out a formula, would a VBA solution be acceptable?



:

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

:

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

:

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

:

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

:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
T

Toppers

Try the following which works if the format is:

David Jones (1st - 12th)

i.e. there must be brackets round the date

Right click on the worksheet tab, view code and copy the code below.

When data is entered in column A, the days will be put in column B.

HTH

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n1 As Integer, n2 As Integer, sn As Integer, fn As Integer
Dim svalue As String

On Error GoTo wsexit
Application.EnableEvents = False

If Target.Column <> 1 Then GoTo wsexit

svalue = Replace(Target.Value, " ", "")
n1 = InStr(1, svalue, "(") + 1
n2 = InStr(1, svalue, ")") - 1
svalue = Mid(svalue, n1, n2 - n1 + 1)
n1 = InStr(1, svalue, "-")
sn = Evaluate(Mid(svalue, 1, n1 - 3))
fn = Evaluate(Mid(svalue, n1 + 1, Len(svalue) - n1 - 2))
Cells(Target.Row, "B") = fn - sn + 1

wsexit:
Application.EnableEvents = True
End Sub
 
K

Kelly Lim

This solution is fantastic, which part in the code should i change? if David
Jones (1st - 12th) needs to be in Column B instead of A and the days to be
put in Column C.

Kelly
 
K

Kelly Lim

Good day,

Mind to give me a hand on this? tried to edit the code, but i cant seems to
get the name&date to Column B instead in A and the days to be in Column C.

Thanks a lot.
Kelly
 
T

Toppers

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n1 As Integer, n2 As Integer, sn As Integer, fn As Integer
Dim svalue As String

On Error GoTo wsexit
Application.EnableEvents = False

If Target.Column <> 2 Then GoTo wsexit '<== Not column B

svalue = Replace(Target.Value, " ", "")
n1 = InStr(1, svalue, "(") + 1
n2 = InStr(1, svalue, ")") - 1
svalue = Mid(svalue, n1, n2 - n1 + 1)
n1 = InStr(1, svalue, "-")
sn = Evaluate(Mid(svalue, 1, n1 - 3))
fn = Evaluate(Mid(svalue, n1 + 1, Len(svalue) - n1 - 2))
Cells(Target.Row, "C") = fn - sn + 1 '<=== days in column C

wsexit:
Application.EnableEvents = True
End Sub
 
Top