• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Take a range and then create a list of all dates

Hi
I have a file that looks like this - take a range and then create a list of all dates in the range taking into consideration the time.

Please see attached file.

Can someone help please, and add the logic to do so.

I have 1000s of rows.

Thanks

David.
 

Attachments

  • Date Range List.xls
    36.5 KB · Views: 23
Thanks
but that does not help
I need to creates a list, one date under the other in Excel, based on the file attached
Can someone help please, and maybe attached an amended file.
thanks in advance.
 
Once you've established the list, what do you intend to do with it? It would appear that it's a stop along the way to a solution, but you might get a better result from identifying the end goal?
 
Hi
I have another list of the dates (vacation) from another system and then I can do a vlookup .
At present it is not possible.
I want to see if both system match up, since there is no automated interface.

The other system lists data by date and NOT in ranges.

I hope that makes sense.
 
Create a macro with name vacation.

i.e., Alt+F8 , type macro name as vacation and press create.

type the following code.

Code:
Sub vacation()
  Do While ActiveCell.Text <> ""
    DAYS = ActiveCell.Offset(0, 2).FormulaR1C1 - ActiveCell.FormulaR1C1
    For I = 1 To DAYS
      ActiveCell.Offset(1, 0).Range("A1").Select
      Selection.EntireRow.Insert
      ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Next I
    ActiveCell.Offset(1, 0).Range("A1").Select
  Loop
End Sub

Close the macro window.

Select First cell containing the start date. In your file C2.

run the macro. <Alt+F8 + Run>.

You will get the desired result.

with best regards

Arun N
 
can I add a complication,
- where the start time is after 6 pm, start from the following day
- where the end date is before 9.am do not include the end date
 
Try………..

A formula solution with helper column way.

1] Helper G2, formula copy down :

=IF(E2<>"",E2-C2+1,"")

2] K2, formula copy down :

=IF(ROW(A1)<=SUM($G$2:$G$10),LOOKUP(ROW(A1),SUMIF(OFFSET(G$1,,,ROW($1:$5),),"<>")+1,C$2:C$15)+ROW(A1)-LOOKUP(ROW(A1),SUMIF(OFFSET(G$1,,,ROW($1:$5),),"<>")+1),"")

3] I2, formula copy across to J2, then copy to L2 and copy across to N2, all copy down :

=IFERROR(INDEX(A$2:A$10,MATCH($K2,$C$2:$C$10,0)),"")

Regards
Bosco
 

Attachments

  • Date Range List.xls
    36.5 KB · Views: 6
can I add a complication,

- where the start time is after 6 pm, start from the following day
- where the end date is before 9.am do not include the end date

therefore
March 09, 2016 on row 3 would start at March 10th.

hope this is clear.
 
can I add a complication,
- where the start time is after 6 pm, start from the following day
- where the end date is before 9.am do not include the end date

If you do not include start date original data will be deleted hence not advisable.
However, end date condition is ok. The VBA code is

Code:
Sub vacation()
  Do While ActiveCell.Text <> ""
    DAYS = ActiveCell.Offset(0, 2).FormulaR1C1 - ActiveCell.FormulaR1C1
    If ActiveCell.Offset(0, 3).FormulaR1C1 < 0.375 Then DAYS = DAYS - 1
    For I = 1 To DAYS
      ActiveCell.Offset(1, 0).Range("A1").Select
      Selection.EntireRow.Insert
      ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Next I
    ActiveCell.Offset(1, 0).Range("A1").Select
  Loop
End Sub

Time to be represented as fraction of a day. Hence, 9AM is 9/24=0.375.
 
can I add a complication,
- where the start time is after 6 pm, start from the following day
- where the end date is before 9.am do not include the end date

If you start the Trip data in next line of original data, the code will be:

Code:
Sub vacation1()
  Do While ActiveCell.Text <> ""
    DAYS = ActiveCell.Offset(0, 2).FormulaR1C1 - ActiveCell.FormulaR1C1
    If ActiveCell.Offset(0, 3).FormulaR1C1 < 0.375 Then DAYS = DAYS - 1
   
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.EntireRow.Insert
   
    If ActiveCell.Offset(-1, 1).FormulaR1C1 <= 0.75 Then
      ActiveCell.FormulaR1C1 = "=R[-1]C"
    Else
      If DAYS > 0 Then
        ActiveCell.FormulaR1C1 = "=R[-1]C+1"
        DAYS = DAYS - 1
      End If
    End If
   
    For I = 1 To DAYS
      ActiveCell.Offset(1, 0).Range("A1").Select
      Selection.EntireRow.Insert
      ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Next I
    ActiveCell.Offset(1, 0).Range("A1").Select
   
  Loop
End Sub

Only issue will be if trip starts after 6pm and ends before 9am, there will be a blank line.
 
can I add a complication,

- where the start time is after 6 pm, start from the following day
- where the end date is before 9.am do not include the end date

therefore
March 09, 2016 on row 3 would start at March 10th.

hope this is clear.
Try………..

Formula solution will be changed as per revised specification.

1] "Adjusted Beginning Date" G2, formula copy down :

=IF(C2<>"",C2+(D2>--"6:00 PM"),"")

2] "No. of days of Trip Segment" H2, formula copy down :

=IF(G2<>"",E2-G2+1,"")

3] L2, formula copy down :

=IF(ROW(A1)<=SUM($H$2:$H$10),LOOKUP(ROW(A1),SUMIF(OFFSET(H$1,,,ROW($1:$5),),"<>")+1,G$2:G$15)+ROW(A1)-LOOKUP(ROW(A1),SUMIF(OFFSET(H$1,,,ROW($1:$5),),"<>")+1),"")

4] J2, formula copy to K2, M2, N2 & O2, and all copy down :

=IFERROR(1/(1/INDEX(A$2:A$10,IFERROR(MATCH($L2,$C$2:$C$6,0),MATCH($L2,$G$2:$G$10,0)))),"")

Regards
Bosco
 

Attachments

  • Date Range List1.xls
    44 KB · Views: 4
If you start the Trip data in next line of original data, the code will be:

Code:
Sub vacation1()
  Do While ActiveCell.Text <> ""
    DAYS = ActiveCell.Offset(0, 2).FormulaR1C1 - ActiveCell.FormulaR1C1
    If ActiveCell.Offset(0, 3).FormulaR1C1 < 0.375 Then DAYS = DAYS - 1
  
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.EntireRow.Insert
  
    If ActiveCell.Offset(-1, 1).FormulaR1C1 <= 0.75 Then
      ActiveCell.FormulaR1C1 = "=R[-1]C"
    Else
      If DAYS > 0 Then
        ActiveCell.FormulaR1C1 = "=R[-1]C+1"
        DAYS = DAYS - 1
      End If
    End If
  
    For I = 1 To DAYS
      ActiveCell.Offset(1, 0).Range("A1").Select
      Selection.EntireRow.Insert
      ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Next I
    ActiveCell.Offset(1, 0).Range("A1").Select
  
  Loop
End Sub

Only issue will be if trip starts after 6pm and ends before 9am, there will be a blank line.
If you start the Trip data in next line of original data, the code will be:

Code:
Sub vacation1()
  Do While ActiveCell.Text <> ""
    DAYS = ActiveCell.Offset(0, 2).FormulaR1C1 - ActiveCell.FormulaR1C1
    If ActiveCell.Offset(0, 3).FormulaR1C1 < 0.375 Then DAYS = DAYS - 1
  
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.EntireRow.Insert
  
    If ActiveCell.Offset(-1, 1).FormulaR1C1 <= 0.75 Then
      ActiveCell.FormulaR1C1 = "=R[-1]C"
    Else
      If DAYS > 0 Then
        ActiveCell.FormulaR1C1 = "=R[-1]C+1"
        DAYS = DAYS - 1
      End If
    End If
  
    For I = 1 To DAYS
      ActiveCell.Offset(1, 0).Range("A1").Select
      Selection.EntireRow.Insert
      ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Next I
    ActiveCell.Offset(1, 0).Range("A1").Select
  
  Loop
End Sub

Only issue will be if trip starts after 6pm and ends before 9am, there will be a blank line.
If you start the Trip data in next line of original data, the code will be:

Code:
Sub vacation1()
  Do While ActiveCell.Text <> ""
    DAYS = ActiveCell.Offset(0, 2).FormulaR1C1 - ActiveCell.FormulaR1C1
    If ActiveCell.Offset(0, 3).FormulaR1C1 < 0.375 Then DAYS = DAYS - 1
  
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.EntireRow.Insert
  
    If ActiveCell.Offset(-1, 1).FormulaR1C1 <= 0.75 Then
      ActiveCell.FormulaR1C1 = "=R[-1]C"
    Else
      If DAYS > 0 Then
        ActiveCell.FormulaR1C1 = "=R[-1]C+1"
        DAYS = DAYS - 1
      End If
    End If
  
    For I = 1 To DAYS
      ActiveCell.Offset(1, 0).Range("A1").Select
      Selection.EntireRow.Insert
      ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Next I
    ActiveCell.Offset(1, 0).Range("A1").Select
  
  Loop
End Sub

Only issue will be if trip starts after 6pm and ends before 9am, there will be a blank line.
 
Hi All

So far brilliant,
But now since we are creating new lines I have a lot of gap between each line and I need to go and manually drop down the employee number for each of the lines, can we make it so that it auto-fills with the data above.

I have 1000 rows so that would take me a long long time.
 
If you start the Trip data in next line of original data, the code will be:

Code:
Sub vacation1()
  Do While ActiveCell.Text <> ""
    DAYS = ActiveCell.Offset(0, 2).FormulaR1C1 - ActiveCell.FormulaR1C1
    If ActiveCell.Offset(0, 3).FormulaR1C1 < 0.375 Then DAYS = DAYS - 1
  
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.EntireRow.Insert
  
    If ActiveCell.Offset(-1, 1).FormulaR1C1 <= 0.75 Then
      ActiveCell.FormulaR1C1 = "=R[-1]C"
    Else
      If DAYS > 0 Then
        ActiveCell.FormulaR1C1 = "=R[-1]C+1"
        DAYS = DAYS - 1
      End If
    End If
  
    For I = 1 To DAYS
      ActiveCell.Offset(1, 0).Range("A1").Select
      Selection.EntireRow.Insert
      ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Next I
    ActiveCell.Offset(1, 0).Range("A1").Select
  
  Loop
End Sub

Only issue will be if trip starts after 6pm and ends before 9am, there will be a blank line.


It still shows March 9th and this should not be shown
 
Is it possible to to do auto complete so all the gaps (new lines) for the travel ID and personal ID are automatically completed. These are blank, and I would need to copy the above line.
A lot for 1000 rows.
 
Hi All

So far brilliant,
But now since we are creating new lines I have a lot of gap between each line and I need to go and manually drop down the employee number for each of the lines, can we make it so that it auto-fills with the data above.

I have 1000 rows so that would take me a long long time.

I think you are mentioning column A. Simply select all the cells in column A upto the row of last data including blank rows.

Press Ctrl+G and Alt+S for Goto Special.
Press Alt+K to select blanks and press enter.
Cursor will be in first blank cell i.e., A3 in our case.
Simply type =A2 in cell A3 and press Ctrl+Enter.
Details will be auto filled. Copy the same and paste as value.

You can repeat the same for Column B Also.

with best regards

Arun N
 
Back
Top