| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
any formula to auto calculate 1st-12th is 12 days pls?
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 as i cannot get the name out once i add in (no name): to the formula. Kelly "Toppers" wrote: 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" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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" wrote: 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 as i cannot get the name out once i add in (no name): to the formula. Kelly "Toppers" wrote: 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" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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" wrote: 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" wrote: 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 as i cannot get the name out once i add in (no name): to the formula. Kelly "Toppers" wrote: 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" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 "Toppers" wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 "Kelly Lim" wrote: 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 "Toppers" wrote: 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
any formula to auto calculate 1st-12th is 12 days pls?
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 "Kelly Lim" wrote: 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 "Kelly Lim" wrote: 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 "Toppers" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to count days | Excel Discussion (Misc queries) | |||
| Need formula to calculate days between dates or back date | Excel Discussion (Misc queries) | |||
| FORMULA NEEDED TO COUNT DAYS IN EXCEL BETWEEN DATES | Excel Worksheet Functions | |||
| Formula for # of sales days in a month? | Excel Worksheet Functions | |||
| HOW TO CALCULATE THE DAYS? | Excel Worksheet Functions | |||
Linear Mode
