Dates Add the Range Dates

[david gabra]

Hi
Can some please help and let me know how to split a date range so we can see all the dates between the ranges, - I have included an example.
I had an solution before that I could not get working.
I have 1000s of rows - so I have add a few and hopefully I will just copy and paste the solution.
Many thanks for your time and effort
- Note -if time is after 6pm to go to start at the next day (if too complicated- you can leave this out)
- If the retun date before 11:59 to exclude that date and use the day before.
D

 

[Chihiro]

What do you mean by this?

- If the retun date before 11:59 to exclude that date and use the day before.
D

 

[vletm]

Press [Personnel Number]-button (cell A1) and
You will get the result to 'new'-sheet

 

[Chihiro]

Ah I see what he meant now.

@vletm
Shouldn't clk_12 be TimeSerial(11, 59, 0)?
And 12 in following changed to clk_12?

If chk_F < 12 Then chk_E = chk_E - 1

 

[vletm]

@Chihiro sometimes ... many times
1) before 11:59 would mean before 12:00:00,
that won't be the biggest challenge just now
2) F-column values present times ... 0 ... <1, then 12 o'clock is 0,5
12 would mean 12 days = 12*24hrs ... or how?
3) if someone has 1000s of rows
... then there would be maybe some other layout suitable, who knows?

 

[Chihiro]

1) That's what I thought too. But OP had following in his sample.


Edit: Doh! Should have read it more carefully. "<" 12:00 i.e. "<=" 11:59

2) I meant that you had 12 in there by mistake.


3) Actually, did small test for about 2000+ lines. Your code is done within few seconds.

 

[vletm]

Oh yes ... now I got something
1) If there are rules about those time like His Cell P11 '04:11:00', many things would be more clear.
2) TYPO ... I changed idea of checking few times and I didn't check after that 'all' - Thanks.
3) Maybe fast enough? ... but I would like to get another layout, or who knows how he would like to use it?
4) newer version uploaded
>> Thanks again ..
ps. Is somewhere any list or something where could see "who/where" use ";" instead of "," ex =MATCH($M13;$G$2:$G$10;0) or = MATCH($M13,$G$2:$G$10,0)
with formulas (not with VBA). ... or am I only one who uses ";"?

 

[david gabra]

What do you mean by this?

I meant to say if the person returns before 11:59 am -then to to change the return date to the day before.

 

[david gabra]

Amazing - thank you - it will be used for a test to see if there are people reporting inaccurate vacation.
Thanks so much to all.

 

[vletm]

How do someone writes time like 11:59:59?
You show times with seconds too
Why there is that 60 seconds before midday, why just not before midday?
Not challenge for me ... just modify if need.

 

[david gabra]

I tried - can you modify so that if there are more columns it will also do for those too. I tired but with no success.

 

[vletm]

What do You mean 'if there are more columns it will also do for those too.'?
I understand the last sentence.
This needs a sample file!

 

[david gabra]

I will add a sample file now - few mins - many thanks

 

[david gabra]

Please see an example now.
Here you can see- that your help was great. I don't really undetstand it fully - but it does the job.
If I need to add extra columns, I would like these too so that they will be included and that it work.. I have 4 extra, 7 extra, no sure, just that your script will work on all column in the excel.
many thanks.

 

[vletm]

Something like this ... with added colums.
... still something no match for me ... ?

 

[david gabra]

you are brilliant

 

[david gabra]

Hi
Is it possible please to help to show exceptions where we have the following condition - who are charging the same expense type, as follows

a) different staff (column a)
b) charge the same amount (column H) -
c) same currency (column I)
c)for the same expense type (column F)
d) for the same travel period (column D and E)

Can the exception (all data in the rows) be copied to new tabs, or I can do this manually.'Here we should have 2 tabs - one for flight exception and 1 for the meals.

or if that is not possible, maybe create a column for each exception type.

 

[david gabra]

Please ignore -- posted to a different group.- thanks