Hello
First of all sorry for my bad english and thanks for helping.
So here is my problem how can i do automaticly and not more by hand ?
I have extract data from one software which gives me working hours into excel files. But this extract file is not formating good. So have done a new files with good formating and then cut an dpast data to this files for evry month.
Then i have doen a new excel files where i done in power query 1 table from all this month to have all years in 1 files and no more 1 files for one year. And this works as great.
So i made a new excel files where i put data from 3 others source an dthis works all great.
But i have a big problem where i have for one date where 1 start time and end time are on same date but other start time is on same date but end time is on next day.
So i have resultat that for that day i have a good resultat for this where start and end time are on same day. But for other not.
In fact i have a row for exemple
5/6/22 start at 6 and end time 5/6 at 12 am and then next row ==> For this i have calculating good 6 hours
5/6/22 start time at 7 pm and end time on 6/6/22 at 5 am. ==> but for this not because
i have first for 5/6/22 calculating midnight - 7pm gives me 5 hours and then for 6/6/22 a i have from 0 to 5 five hours
But what i want that if on date there is no start time but thers is end time calclating this hours opn previos days
So i will have 2 rows one for 6 am to 12pm on 5/6/22 and other for 5/6/22 which goes from 7pm to 5 am and gives me 10 hours and row for 6/5/22 where thers is no start time errase that row.
Here on image you can see my power queery table :
And here image of my excel file where i put those data from powerquery table as source :
So as you can see on 6/6/22 it is showing 2:10 for end time only and is calculatig hours for this day but this hours belong to previous day for start time which start at 19:55.
For now i calulating manually for exemple 04:04+02:10 = 06:14 and then i put this 6:14 instead 04:04 and errase all data for row 06/6/22
One more question also have can be done if on power query for one week there is more then 7 days (2 times same days) How can i add extra row via vba to range of this week. For exemple from 6/6 to 12/6 there is 7 row plus when for total for week. So if i had for 9/6 2 start time on powery query source excel put this extra row inside a range and fix sumup which goes from row 7 to row 1 to be from row 8 to row 1 ? But this is not urgent.
Thank you for help and sorry if i put so long text to explain it and hope your understand it.
First of all sorry for my bad english and thanks for helping.
So here is my problem how can i do automaticly and not more by hand ?
I have extract data from one software which gives me working hours into excel files. But this extract file is not formating good. So have done a new files with good formating and then cut an dpast data to this files for evry month.
Then i have doen a new excel files where i done in power query 1 table from all this month to have all years in 1 files and no more 1 files for one year. And this works as great.
So i made a new excel files where i put data from 3 others source an dthis works all great.
But i have a big problem where i have for one date where 1 start time and end time are on same date but other start time is on same date but end time is on next day.
So i have resultat that for that day i have a good resultat for this where start and end time are on same day. But for other not.
In fact i have a row for exemple
5/6/22 start at 6 and end time 5/6 at 12 am and then next row ==> For this i have calculating good 6 hours
5/6/22 start time at 7 pm and end time on 6/6/22 at 5 am. ==> but for this not because
i have first for 5/6/22 calculating midnight - 7pm gives me 5 hours and then for 6/6/22 a i have from 0 to 5 five hours
But what i want that if on date there is no start time but thers is end time calclating this hours opn previos days
So i will have 2 rows one for 6 am to 12pm on 5/6/22 and other for 5/6/22 which goes from 7pm to 5 am and gives me 10 hours and row for 6/5/22 where thers is no start time errase that row.
Here on image you can see my power queery table :
And here image of my excel file where i put those data from powerquery table as source :
So as you can see on 6/6/22 it is showing 2:10 for end time only and is calculatig hours for this day but this hours belong to previous day for start time which start at 19:55.
For now i calulating manually for exemple 04:04+02:10 = 06:14 and then i put this 6:14 instead 04:04 and errase all data for row 06/6/22
One more question also have can be done if on power query for one week there is more then 7 days (2 times same days) How can i add extra row via vba to range of this week. For exemple from 6/6 to 12/6 there is 7 row plus when for total for week. So if i had for 9/6 2 start time on powery query source excel put this extra row inside a range and fix sumup which goes from row 7 to row 1 to be from row 8 to row 1 ? But this is not urgent.
Thank you for help and sorry if i put so long text to explain it and hope your understand it.
