PDA

View Full Version : [SOLVED] Dates



gibbo1715
02-13-2005, 01:54 AM
I ve never been good with dates and wondered how to do this

I want to use a user form with two text boxes to create a time line as follows

Input a date and time (one at a time) and have it put onto row 4 ( second text box will contain the data that will go in the cell below (offset(1,0)).

I want to ensure the earliest date is always in column B. I would then like each time I add a new date to put it in the right place on the time line, but i also want to leave a row between each date so for example B4 = 11/11/2003 09.00 - C4 - "" - D4 = 11/12/2003 10.00 - E4 = "" - F4 = 11/12/2003 10.20 and so on .....

Anyone think of an easy way of doing this

Jacob Hilderbrand
02-13-2005, 02:35 AM
I think you have your cells wrong. Do you mean:

B4 = Date

B5 = ""

B6 = Date

B7 = ""

Or do you mean something else? If this is the way you want the data setup I would suggest you try to use a different layout. Use one column for dates and one for values that way you can just sort by the date column.

Depending on how you want to input the date and time, you will probably need to convert the time to the fractional value that Excel uses to store time.

1 Day = 1

1 Hour = 1/24

1 Minute = 1/(24*60)

1 Second = 1/(24*60*60)

gibbo1715
02-13-2005, 02:45 AM
Id thought of doing it the way you suggested, but really i want the time line to go across the screen Jake thats whats stumping me.

Any Ideas?

Jacob Hilderbrand
02-13-2005, 04:16 AM
So do you want the time in every other column? This will make it very difficult to sort, though not impossible. You do realize that you will be limiting yourself to 128 dates/times since you will run out of columns?

Can you make up a workbook that better illustrates what you want to do and attach it?

gibbo1715
02-13-2005, 05:27 AM
Thanks Jake

I ve attached a worksheet to show you where I want the date/time to go

The upper limit is no problem as my time lines wont ever be that long

Many Thanks

Jacob Hilderbrand
02-13-2005, 07:07 PM
Try this.



Option Explicit

Private Sub CommandButton1_Click()
Dim DateCol As New Collection
Dim TextCol As New Collection
Dim i As Long
Dim n As Long
Dim Counter As Long
Dim LastCol As Long
Dim AppExcel As New Excel.Application
Dim Wkb As Workbook
Set Wkb = AppExcel.Workbooks.Add
LastCol = Range("IV4").End(xlToLeft).Column + 1
Cells(4, LastCol).Value = Me.TextBox1.Value
Cells(4, LastCol + 1).Value = Me.TextBox2.Text
LastCol = LastCol + 2
For i = 2 To LastCol Step 2
DateCol.Add Cells(4, i).Value
TextCol.Add Cells(4, i + 1).Value
Next i
n = DateCol.Count
With AppExcel.Sheets(1)
For i = 1 To n
.Range("A" & i).Value = DateCol(i)
.Range("B" & i).Value = TextCol(i)
Next i
.Range("A1:B" & n).Sort Key1:=.Range("A1"), _
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Counter = 2
For i = 1 To n
Cells(4, Counter).Value = .Range("A" & i).Value
Cells(4, Counter + 1).Value = .Range("B" & i).Value
Counter = Counter + 2
Next i
End With
Wkb.Close False
AppExcel.Quit
Set Wkb = Nothing
Set AppExcel = Nothing
Unload Me
End Sub

Refer to the attachment for an example.

Check Here (http://www.vbaexpress.com/kb/getarticle.php?kb_id=21) for an example of how to use a Calendar Control to allow a user to select a date.

gibbo1715
02-14-2005, 12:24 AM
Thaks Jake, only thing is i need the details to be below the date not next to it if possible

Many thanks

Jacob Hilderbrand
02-14-2005, 12:29 AM
Try this.


Option Explicit

Private Sub CommandButton1_Click()
Dim DateCol As New Collection
Dim TextCol As New Collection
Dim i As Long
Dim n As Long
Dim Counter As Long
Dim LastCol As Long
Dim AppExcel As New Excel.Application
Dim Wkb As Workbook
Set Wkb = AppExcel.Workbooks.Add
LastCol = Range("IV4").End(xlToLeft).Column + 1
Cells(4, LastCol).Value = Me.TextBox1.Value
Cells(5, LastCol).Value = Me.TextBox2.Text
LastCol = LastCol + 2
For i = 2 To LastCol Step 2
DateCol.Add Cells(4, i).Value
TextCol.Add Cells(5, i).Value
Next i
n = DateCol.Count
With AppExcel.Sheets(1)
For i = 1 To n
.Range("A" & i).Value = DateCol(i)
.Range("B" & i).Value = TextCol(i)
Next i
.Range("A1:B" & n).Sort Key1:=.Range("A1"), _
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Counter = 2
For i = 1 To n
Cells(4, Counter).Value = .Range("A" & i).Value
Cells(5, Counter).Value = .Range("B" & i).Value
Counter = Counter + 2
Next i
End With
Wkb.Close False
AppExcel.Quit
Set Wkb = Nothing
Set AppExcel = Nothing
Unload Me
End Sub

gibbo1715
02-14-2005, 01:29 AM
thanks jake but its not working quite right, initially it puts a gap in c4 but then fills it and doesnt put the gaps between each date, just puts them into the next cell, also doesnt sort the dates

Jacob Hilderbrand
02-14-2005, 02:00 AM
Try this.


Option Explicit

Private Sub CommandButton1_Click()
Dim DateCol As New Collection
Dim TextCol As New Collection
Dim i As Long
Dim n As Long
Dim Counter As Long
Dim LastCol As Long
Dim AppExcel As New Excel.Application
Dim Wkb As Workbook
Set Wkb = AppExcel.Workbooks.Add
LastCol = Range("IV4").End(xlToLeft).Column + 2
Cells(4, LastCol).Value = Me.TextBox1.Value
Cells(5, LastCol).Value = Me.TextBox2.Text
LastCol = LastCol + 2
For i = 2 To LastCol Step 2
DateCol.Add Cells(4, i).Value
TextCol.Add Cells(5, i).Value
Next i
n = DateCol.Count
With AppExcel.Sheets(1)
For i = 1 To n
.Range("A" & i).Value = DateCol(i)
.Range("B" & i).Value = TextCol(i)
Next i
.Range("A1:B" & n).Sort Key1:=.Range("A1"), _
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Counter = 2
For i = 1 To n
Cells(4, Counter).Value = .Range("A" & i).Value
Cells(5, Counter).Value = .Range("B" & i).Value
Counter = Counter + 2
Next i
End With
Wkb.Close False
AppExcel.Quit
Set Wkb = Nothing
Set AppExcel = Nothing
Unload Me
End Sub

gibbo1715
02-14-2005, 03:01 AM
thank you jake, puts the dates with the gap between them but doesnt sort them, i note the sort range is set to A1?




.Range("A1:B" & n).Sort Key1:=.Range("A1"), _
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom


there is nothing in cell A1?

I am as usual a little confussed

Any Ideas?

Jacob Hilderbrand
02-14-2005, 03:09 AM
It sorts them fine for me. Maybe they are not being inputted as dates for you? Add a date from the User Form and then try this formula.


=ISNUMBER(B4)

A1 is the sort key becase all the data is added to a new workbook starting @ A1 then sorted, then put back into your workbook.

gibbo1715
02-14-2005, 04:07 AM
seems to be putting the dates in starting at C4 not B4 for me, i checked and it is in the correct format to sort?

gibbo1715
02-14-2005, 04:10 AM
should add if i delete column B it sorts fine

Jacob Hilderbrand
02-14-2005, 04:16 AM
Ok, I think I see the problem. Try this.


Option Explicit

Private Sub CommandButton1_Click()
Dim DateCol As New Collection
Dim TextCol As New Collection
Dim i As Long
Dim n As Long
Dim Counter As Long
Dim LastCol As Long
Dim AppExcel As New Excel.Application
Dim Wkb As Workbook
Set Wkb = AppExcel.Workbooks.Add
LastCol = Range("IV4").End(xlToLeft).Column
If LastCol = 1 Then
LastCol = 2
Else
LastCol = LastCol + 2
End If
Cells(4, LastCol).Value = Me.TextBox1.Value
Cells(5, LastCol).Value = Me.TextBox2.Text
For i = 2 To LastCol Step 2
DateCol.Add Cells(4, i).Value
TextCol.Add Cells(5, i).Value
Next i
n = DateCol.Count
With AppExcel.Sheets(1)
For i = 1 To n
.Range("A" & i).Value = DateCol(i)
.Range("B" & i).Value = TextCol(i)
Next i
.Range("A1:B" & n).Sort Key1:=.Range("A1"), _
Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Counter = 2
For i = 1 To n
Cells(4, Counter).Value = .Range("A" & i).Value
Cells(5, Counter).Value = .Range("B" & i).Value
Counter = Counter + 2
Next i
End With
Wkb.Close False
AppExcel.Quit
Set Wkb = Nothing
Set AppExcel = Nothing
Unload Me
End Sub

gibbo1715
02-14-2005, 04:24 AM
Thanks Jake, that works absolutly great,

just one more question and i ll leave you in peace,

How easy is it to make it sort time and date and not just time, i ve changed the format from dd/mm/yyyy to dd/mm/yyyy hh:mm but still only sorts on the date ignores the time?

Jacob Hilderbrand
02-14-2005, 04:34 AM
Ok, I just tested it with time and it works just fine. You need to make sure that the time is inputted correctly.

1/5/2004 12:30:00 AM

Make sure you add the AM/PM when you are inputting the date/time.

gibbo1715
02-14-2005, 05:02 AM
Thanks Jake, i was only inputting 12:00 so theres my mistake

Solved once again

Jacob Hilderbrand
02-14-2005, 05:05 AM
You're Welcome :beerchug:

Take Care