input box to worksheet calculation...

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
Hi all,

Here's my problem,

I have a created a Form for users to enter client details and billing info that feeds to a worksheet.

On the form I have an input box for an estimated billing date (EBD).

What I'm trying to do (with little success) is to create a calculation on the form which works out the number of days to billing (ie EBD - NOW()
Then
On the work sheet drop it in one of 4 cells depending on how many days the calculation is. (i.e between 1 -30,31-60,61-90,>90)...

Am pulling my hair out... so if anyone has a brilliant idea I would love to hear from you.

Thanks
Matt
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
I've tried IF's to work out the number of days (i.e =IF(AND($K$4>60,$K$4<=90),"X","")
which is fine...
I don't know how to get this to intact with the input box on the form...
So, when the user types in an date, and clicks sumit, the IF goes to work then, calculates the days between NOW() and the date input and then throws it into one of 4 defined cells (see first post)...
ta
M
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
On 2002-11-05 09:01, mrowe wrote:
Hi all,

Here's my problem,

I have a created a Form for users to enter client details and billing info that feeds to a worksheet.

On the form I have an input box for an estimated billing date (EBD).

What I'm trying to do (with little success) is to create a calculation on the form which works out the number of days to billing (ie EBD - NOW()
Then
On the work sheet drop it in one of 4 cells depending on how many days the calculation is. (i.e between 1 -30,31-60,61-90,>90)...

Am pulling my hair out... so if anyone has a brilliant idea I would love to hear from you.

Thanks
Matt


Matt you could do this calculation as part of the InputBox in the Macro eg

EBD = InputBox("Date")

Range("a3") = Format(EBD, "MM/DD/YY")

AGEDDATA = Range("A1") - Range("A3")
where A1 = Today()


If AGEDDATA <= 30 Then Range("B1") = AGEDDATA
If AGEDDATA > 30 And AGEDDATA <= 60 Then Range("C1") = AGEDDATA
If AGEDDATA > 60 And AGEDDATA <= 90 Then Range("D1") = AGEDDATA
If AGEDDATA > 90 Then Range("E1") = AGEDDATA

End Sub
 

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232

ADVERTISEMENT

Thank you.. this looks like it will do the trick.. thanks for your help
Matt
 

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
hi... sorry but one more question... I've now been asked to have the form object as a textBox... I've tried things like EBD = textbox9 etc but get data mismatch errors - do I have to dim As something??
Thanks in advance.
Matt
 

Forum statistics

Threads
1,144,310
Messages
5,723,632
Members
422,505
Latest member
Noar33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top