Splitting rows in a table based on date fields.

[Tom123456]

Hey,

I have a list of bookings that have a start & end date and a amount.

I need to split the table so that each month has it's own line and % amount

For example before it would look like this.

Booking	Start Date	End Date
Booking 1	01/08/2017	30/11/2017
Booking 1	01/10/2017	30/11/2017
Booking 2	11/08/2017	30/09/2017
Booking 2	11/08/2017	30/09/2017
Booking 3	07/08/2017	31/08/2017

<tbody>
</tbody>

but i need to work out how to split it to become like so

Booking	Start Date	End Date
Booking 1	01/08/2017	31/08/2017
Booking 1	01/09/2017	30/09/2017
Booking 2	11/08/2017	31/08/2017
Booking 2	01/09/2017	30/09/2017
Booking 2	11/08/2017	31/08/2017
Booking 2	01/09/2017	30/09/2017
Booking 3	07/08/2017	31/08/2017

<tbody>
</tbody>

I've added columns that calculates the number of days/month in each row and the amount per day but don't know where to start with using this information to split like this- if anyone can point me in the right direction it would be a great help! Cheers, Tom

<tbody>
</tbody>

 

[Tom123456]

Superb - thanks again for you help!

 

[Joe4]

You are welcome.

 

[Tom123456]

Hi Joe,

Hope your well.

I've noticed that the Start Date field in 'Dest Table' is generating the date in the American format so where the output should be 1st September (01/09) it displays (09/01) - Do you know how the process can be amended so it generates and displayed the date in UK format? It works as intended for the End Date field so not sure where it needs to be changed.

Cheers,
Tom

 

[Joe4]

I believe the issue is that VBA always uses US format.

Is the date wrong, or just formatted wrong?
If you apply a Custom Format to your table of something like "mmm-dd-yyyy", it should be evident if it is returning "September" or January".
If it is returning the correct date, just change the date format in the table to get it to look the way you want.

 

[Tom123456]

Hi Joe,

I've applied a custom format on the table and it looks like the date is actually wrong for the start date field - however it's the correct format for the end date field that was also generated from VBA

Is there a way to change the VBA so it generates the start date in UK format? if not is there anything that can be done after it's generated to convert to UK format?

Cheers,
Tom

 

[Tom123456]

Just to add in case it helps troubleshooting the end date field only works correctly if the value is after the 12th day of the month (eg 13/02/2017) - if the date is before this it also converts incorrectly for me

I assume the VBA tries to associate the value to a US format first and if not applies the UK date format.

 

[Joe4]

I have seen others have this issue, but have never had to deal with it myself, since I am using US version (so I cannot even test my solutions to see what they would do for you).

If you say the end date is correct, I think I would focus on this file here:

strSQL = strSQL & "VALUES ('" & ref & "', " & lineId & ", '" & sales & "', '" & customer & "', #" & [COLOR="#FF0000"]nStartDt[/COLOR] & "#, #" & nEndDt & "#, '"

Maybe try replacing:
nStartDt
with:
Format(nStartDt,"dd/mm/yyyy")
or
Format(nStartDt,"dd-mmm-yyyy")
You might need to play around with it in order to find the right combination that works.

 

[Tom123456]

Hi Joe,

It looks like this one Format(nStartDt,"dd-mmm-yyyy") works for UK Dates as expected - I've also applied it to the end date column.

Edit: Fixed the issue with customer contract date - was trying to change the format of the column rather then it's input in the VBA script


Cheers,
Tom

 

[Joe4]

It should work the same way as the others.
Did you happen to spill over to a second row, like shown in your post above? That could cause problems, if not done properly.

 

[Tom123456]

No i was trying to reformat the column being created rather then the input value line for that col -i changed where i was formatting the field and it worked as expected.

Thanks again for your help with this query