Creating a date box

Philip290 · #1 05-11-2011, 01:20 AM

Hi, I'm trying to make a Excel sheet that will take data 'days' from one box, and add it to another..

for example:

Days on vacation: x
Date leave: October-20th-2011
Date return: above date + 'days on vacation' so if 7 was entered, it would return the date as October-27th-2011

Short/long form date doesn't matter. Also, how can I use the data on one sheet on another? I want a chart on the second sheet.

Thanks.

macropod · #2 05-11-2011, 02:52 AM

Hi Philip,

Assuming your textboxes are on a userform and are named TextBox1, TextBox2 & TextBox3, with the date in TextBox1 and the # days in TextBox2, try:

Code:

Private Sub TextBox1_AfterUpdate()
If TextBox2.Value = "" Then TextBox2.Value = 0
MsgBox DateAdd("d", CInt(TextBox2.Value), CDate(TextBox1.Text))
TextBox3.Text = Format(DateAdd("d", CInt(TextBox2.Value), CDate(TextBox1.Text)), "DD/MMMM/YYYY")
End Sub
 
Private Sub TextBox2_AfterUpdate()
If TextBox1.Text = "" Then TextBox1.Text = Format(Now, "DD/MMMM/YYYY")
MsgBox DateAdd("d", CInt(TextBox2.Value), CDate(TextBox1.Text))
TextBox3.Text = Format(DateAdd("d", CInt(TextBox2.Value), CDate(TextBox1.Text)), "DD/MMMM/YYYY")
End Sub

Philip290 · #3 05-11-2011, 02:54 AM

Hi macropod,

Thanks for the reply, I'm still a beginner to Excel, so I don't know what textboxes/userforms are; this is just a basic excel sheet using stuff like =Date(...).

Is there any way to do it in a simple fashion?

OTPM · #4 05-11-2011, 02:59 AM

Hi Philip209
You can simply add the entry in one cell to the start date:
Example:
In a new column - A3+A2.
Where A3 is the start date of leave in date format and A2 is the number of days the member of staff is taking off.
Hope this helps.
Tony

Philip290 · #5 05-11-2011, 03:01 AM

Quote:

Originally Posted by OTPM

Hi Philip209
You can simply add the entry in one cell to the start date:
Example:
In a new column - A3+A2.
Where A3 is the start date of leave in date format and A2 is the number of days the member of staff is taking off.
Hope this helps.
Tony

Hi OTPM, that's fantastic and exactly what I was after.

Thanks!

macropod · #6 05-11-2011, 03:27 AM

Hi Philip,

Quote:

Originally Posted by Philip290

I'm still a beginner to Excel, so I don't know what textboxes/userforms are; this is just a basic excel sheet using stuff like =Date(...)

In that case, you need to learn the correct way of describing things. To refer to a textbox necessarily implies the use of a userform or an ActiveX control. If you want to work with cells on a worksheet, don't call them textboxes - call them cells. As you now know, adding a number to a date on a worksheet is very simple. Working with textboxes and userforms is not so simple and the solution is entirely different.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Date pop-up window	Roshan Hanief	Word	0	04-23-2011 04:16 AM
Creating an Auto-Calc'd Date? Today+7 Days?	SoCalTelephone	Word	0	10-06-2010 10:27 AM
Julian Date	windseaker	Excel	1	02-14-2010 01:44 AM
new appointment date always reverts back to today's date	msills	Outlook	0	08-24-2007 08:57 AM
Imported message date change to today's date	promark	Outlook	1	12-23-2005 07:21 AM