Jo Craig
24022018
08:11 AM BST

Can anyone help me with a formula to count days within a month only. I have two columns titled "Start Date" and "Finish Date" eg. Start Date is 22 Jan 18 and Finish Date is 10 Feb 18. Is there a formula to count the number of days (within those two columns) for the month of January 2018 only? It would equal 10 days. I then need another formula, same example as above, to calculate the days that would be returned for February 2018 only, ie. would equal 10 days. The total amount of lines within the speadsheet could be anywhere up to 500 lines, so I would like to count all 500 lines and days counted for months only. Hope that makes sense. Thanks.

Smileyface
06022017
09:57 PM BST

Hi Chris,
Is it easy to "switch" your WordNum Function, so that if the user supplies a value in text, eg one thousand and fifty, it returns the value 1050?
Thanks
captainmarkov@hotmail.com

Iain Whyte
16012017
04:41 PM BST

I used to be able to do names and addresses on Excel. I want to find out how I
a) Close the gaps on the chart with deleted addresses b) Re arrange the surnames alphabetically (I have already inputted several new names and addresses
Very grateful for tips  I cant navigate my way round the Filters, Sorts as I remember before.

John
23112016
04:10 PM BST

Hi Everyone,
Here is an Excel number speller (using some www.meadinkent.co.uk VBA, Visual Basic for Applications, code I tweaked) I wrote that either displays any decimal number entered as a decimal number rounded to thousandths or dollars and cents. I would love to have it read decimals all the way to millionths, billionths, trillionths, etc., but VBA tends to stump me. Lol Any help would be appreciated. My email is treeguywussupmeadinkent@yahoo.com
The sheet uses range B1:C5, cell B1 was named "CurrencySymbol" and limited to a list of "" (i.e., a blank cell or "empty string") or "$." Here are example results followed by formulae and VBA:
Sample Results (with CurrencySymbol = "empty string," ie., "") 1,234,567.891501 1,234,567.891501 one million, two hundred thirtyfour thousand, five hundred sixtyseven eight hundred ninetytwo thousandths one million, two hundred thirtyfour thousand, five hundred sixtyseven and eight hundred ninetytwo thousandths
Sample Results (i.e., with CurrencySymbol = "$"): $ 1,234,567.891501 $1,234,567.89 one million, two hundred thirtyfour thousand, five hundred sixtyseven dollars eightynine cents one million, two hundred thirtyfour thousand, five hundred sixtyseven dollars and eightynine cents
Formulae (i.e., with cell B1 or CurrencySymbol value = "$"): $ 1234567.891501 =IF(CurrencySymbol="$",TEXT(C1,"$#,##0.00"),C1) =wordnum(C1)&IF(CurrencySymbol<>"$",""," dollars") "=IF(CurrencySymbol=""$"",wordnum(ROUND((C1INT(C1)),2)*100)&"" cents"", wordnum(ROUND(($C$1INT($C$1)),3)*1000)&"" thousandths"")" =C3&" and "&C4
VBA  Visual Basic for Applications code, i.e., press Alt+F11! ;) Option Explicit Public Numbers As Variant, Tens As Variant
Sub SetNums() Numbers = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen") Tens = Array("", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety") End Sub
Function WordNum(MyNumber As Double) As String Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String ' This macro was written by Chris Mead  www.MeadInKent.co.uk
If Abs(MyNumber) > 999999999 Then WordNum = "Value too large" Exit Function End If
SetNums
' String representation of amount (excl decimals) NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9) ' NumStr = Right("000000000000" & Trim(Str(Int(Abs(MyNumber)))), 12) '[TRIED MODIFYING 23Nov2016] ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))
For n = 3 To 1 Step 1 'analyse the absolute number as 3 sets of 3 digits StrNo = Format(ValNo(n), "000") ' StrNo = Format(ValNo(n), "000000") '[TRIED MODIFYING 23Nov2016]
If ValNo(n) > 0 Then Temp1 = GetTens(Val(Right(StrNo, 2))) If Left(StrNo, 1) <> "0" Then Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred" ' If Temp1 <> "" Then Temp2 = Temp2 & " and " If Temp1 <> "" Then Temp2 = Temp2 & " " Else Temp2 = "" End If
If n = 3 Then ' If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and " ' If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = ", " If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = " " WordNum = Trim(Temp2 & Temp1) End If If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand, " & WordNum) If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million, " & WordNum)
End If Next n
NumStr = Trim(Str(Abs(MyNumber)))
' Values after the decimal place DecimalPosition = InStr(NumStr, ".") Numbers(0) = "zero" If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then Temp1 = " point" For n = DecimalPosition + 1 To Len(NumStr) Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1))) Next n ' WordNum = WordNum & Temp1 WordNum = WordNum End If
If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then WordNum = "zero" & WordNum End If End Function
Function GetTens(TensNum As Integer) As String ' Converts a number from 0 to 99 into text. If TensNum <= 19 Then GetTens = Numbers(TensNum) Else Dim MyNo As String MyNo = Format(TensNum, "00") ' GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1))) GetTens = Tens(Val(Left(MyNo, 1))) & "" & Numbers(Val(Right(MyNo, 1))) End If End Function


Messages 25592557 deleted by author between 11232016 11:08 AM and 11232016 10:55 AM 
Yomesh Triapthi
23062016
01:33 PM BST

Hi all for Number to text i got one script for excel addin n this place i got from there. i have small issue with it. It's working fine now i was using this to print cheque working fine but 1, it's writing in only one line so if amount are bigger then it change the font size and after the amount in alphabet i need this word and of the line "Only." can you help me for this i am not programmer so i not able to do this part. Option Explicit Public Numbers As Variant, Tens As Variant Sub SetNums() Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen") Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety") End Sub Function WordNum(MyNumber As Double) As String Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String ' This macro was written by Chris Mead  www.MeadInKent.co.uk If Abs(MyNumber) > 999999999 Then WordNum = "Value too large" Exit Function End If SetNums ' String representation of amount (excl decimals) NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9) ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3))) For n = 3 To 1 Step 1 'analyse the absolute number as 3 sets of 3 digits StrNo = Format(ValNo(n), "000") If ValNo(n) > 0 Then Temp1 = GetTens(Val(Right(StrNo, 2))) If Left(StrNo, 1) <> "0" Then Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred" If Temp1 <> "" Then Temp2 = Temp2 & " and " Else Temp2 = "" End If If n = 3 Then If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and " WordNum = Trim(Temp2 & Temp1) End If If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum) If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum) End If Next n NumStr = Trim(Str(Abs(MyNumber))) ' Values after the decimal place DecimalPosition = InStr(NumStr, ".") Numbers(0) = "Zero" If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then Temp1 = " point" For n = DecimalPosition + 1 To Len(NumStr) Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1))) Next n WordNum = WordNum & Temp1 End If If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then WordNum = "Zero" & WordNum End If End Function Function GetTens(TensNum As Integer) As String ' Converts a number from 0 to 99 into text. If TensNum <= 19 Then GetTens = Numbers(TensNum) Else Dim MyNo As String MyNo = Format(TensNum, "00") GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1))) End If End Function Thank's for what i have all ready from this script. Yomeshtripathi@gmail.com Edited 23062016 01:35 PM

Robert Dahlke
18052016
09:31 PM BST

Hi all,
I'm trying to create a student loan calculator in Excel(google sheets) where I can add additional payments but it would only add it for a specific date not every single payment. I've used the formula: =if(day(A27)=Day($B$5),$B$6,"") where A27 is date, B5 is my scheduled monthly payment date, B6 is payment amount. This works for changing my scheduled monthly payment to a specified day of the month throughout the sheet, but what I want to do is allow 20 rows for additional payment dates,
For example B10:B30 (any date for additional payment) C10:C30 (amount of additional payment). A31:A1000 (date in 1 day increments, starts at 1/1/2016, 1/2/2016, etc) D31:D1000 (Additional payment amount). I want to enter 1/2/2016 into B10, $30 into C10 and then $30 is pops into D32. Then enter 1/5/2016 into B11, $40 into C11, then $40 pops up into D35.
Any thoughts? Any help would be greatly appreciated.
Best,
Robert rpdahlke@gmail.com

William
15042016
06:22 PM BST

This is the first time I have used the forum/site. I found it today while search for a database to save all my business contacts. I found the Associate leave planner and thought that it would be a great tool if the titles were editable. Does anyone know if the leave title could be changed to QAV? I would like to use the tool for my Supplier travel Visits if possible. Thanks for the help

Kevin
13072015
01:43 PM BST

Hi there, I have tried using the numbers to text with some success however I wish to use it for UK currency. The 'point' whatever value needs to be in pence and whole numbers i.e. twenty not two, zero.
I have tried a formula to use the WordNum which does actually work quite well but falls over sometimes, for example 11 pence would be 11 pence but 10 pence would be one pence and the zero ignored. The formula is: =CONCATENATE(wordnum(ROUND(L22,0))," POUNDS ",wordnum(RIGHT(L22,2))," PENCE")
Anyway I hope this can be solved!
Many thanks Kevin kjthorley@gmail.com

PUSHKAR P BHATKHANDE
09072015
11:24 AM BST

The number to word conversion code (wordnum) doesn't contai the term "LAC" or "LACS" as well as "Crore" or "Crores". let me please know which changes need to be done in the code for INDIAN CURRENCY STYLE. MY EMail Address is p.bhatkhande@pcmcindia.gov.in or pushkarbh@hotmail.com. please revert back as early as possible.

Rajiv
22052015
05:02 AM BST

Hi, Our Team using Shared Excel file, file contain 1000 data in a row + column (7 to 8 users) few user are using MS Excel 2003 & few are using MS Excel 2007, Many times it's happened file getting locked by some user. Kindly confirm how to avoid such error & why such error is happened in sharing file. Kindly suggest best possible solution & Thanks in advance...!! My email id is Rajiv.rank1@tataaig.com & rajiv.rank85@gmail.com

Jakeneedshelp
21052015
03:51 PM BST

So I'm using excel and I have a question. so I am building a bid worksheet. in sheet1 I have a bunch of crap. in sheet2 I have a bunch of Item names in A2:A120 then In B2:B120 I have the Price of said items. so I was wondering if there is a way or formula so lets say in a cell I type in the word "Honeywell" then in another cell next to it, it prints the price "$$$" any help or guidance would be helpful.

Rajiv
13032015
05:04 AM BST

Hi, Our Team using Shared Excel file, file contain 1000 data in a row + column (7 to 8 users) few user are using MS Excel 2003 & few are using MS Excel 2007, Many times it's happened file getting locked by some user. Kindly confirm how to avoid such error & why such error is happened in sharing file. Kindly suggest best possible solution & Thanks in advance...!! My email id is Rajiv.rank1@tataaig.com & rajiv.rank85@gmail.com

kerry
12032015
01:39 PM BST

I want to provide the option "other" in drop down lists AND if "other" is selected I want a way of allowing space to specify what that "other" is, Is this possible? very many thanks in advance

Rajiv
14012015
08:51 AM BST

Hi, Our Team using Shared Excel file, file contain 1000 data in a row (7 to 8 users) few user are using MS Excel 2003 & few are using MS Excel 2010 Many times it's happened file getting locked by some user. Kindly confirm how to avoid such error & why such error is happened in sharing file. Kindly suggest best possible solution & Thanks in advance...!! My email id is Rajiv.rank1@tataaig.com & rajiv.rank@gmail.com


