Spreading Date per Day

[KamenRider]

Hi,

I am asking for your assistance in helping me out solving date problems. Actually I have a report where date is range and I wanted it to be range per day. If there are dates range by 2 or more days, I would like it be transformed per day, thus it will add more rows to represent per day and number of hours will also change to 8 hours since working hours is 8 per day. Other fields will be copied same. I hope this is possible.

Below is the raw report (which is also being attached)

This should be the results.

Hoping someone to help me this out. Let me know if you have any questions.

Thanks,

Kamen

[binuacs]

@KamenRider One way of doing this

[KamenRider]

Hi @binuacs 

Thanks for the response. Your solution is very close. My only problem is that when the Employee code has duplicate, it counts even if the date is not related or not range. See example below.

Can we somewhat update the workflow you created. The result should look like this.

Duplicate Emp code is possible because they can request for a leave any date they wanted. If it is not a range then, it is considered to be a separate leave.

Please let me know if you have any questions.

Thanks,

Kamen

[binuacs]

@KamenRider updated the workflow, let me know if it works for you

[KamenRider]

Hi @binuacs 

Thanks for the response. I noticed that the workflow is not working for duplicate Emp code that has range dates. The result gives me a total of requested hours and is not being populated to 8 hours or 4 hours per day. Can you please update the workflow. Below is an added scenario.

Looking forward for your revert. Thank you.

Kamen

[binuacs]

@KamenRider How to identify duplicate records with range date and without range date?

[KamenRider]

Hi @binuacs 

See below results coming from the workflow. I added records.

 

You may noticed that there are records specifically those are in ranges that is not populating the dates. I noticed that is because of duplicate Emp Code. Is there a way to fix this one. Results should be for example:

Please let me know if you have any quetions.

Thanks,

Kamen

[binuacs]

@KamenRider My question was from the input file how would you identify the duplicate with date range or without range

what is the difference between the duplicate records 12345 and  78452? I am asking because for 12345 we are just keeping the same record they are not splitting like other records but for record 78452 - the requirement was to split like other records even though it is a duplicate. So I'm just looking for the logic only for the duplicate records which fall under which condition? 

[KamenRider]

Hi @binuacs 

If we are going to look for the original file, it is really hard to identify the duplicate with date range because this has a hundred of employees taking their leave depending on their preferences if it is one day day off or multiple number of days. In a month an employee may plot their leaves twice or thrice either one day or more until they were able to consume their leaves if they want to. (this is the reason why we have duplicate emp id in a month)

If I am to describe emp # 12345 in our sample, he plotted a leave 1 for start date 12-05 and another leave for 12-06 based on requested date. The employee didn't use date range in the system but plotted it separately on 12-29 and 12-30 respectively.

For emp #78452, the employee plotted the leaves in date range for a number of days in the system. When it is in date range, we need to simplify it by showing per day, that is why requested hours will be converted to 8 hours each if the employee take it whole day leave.

How to identify the duplicate with date range is using the absence start date and absence end date.

Duplicate employee # is possible and differs in start date and end date. The condition is that when is in date range, it needs to be simplify and if not, no change.

I hope this answers your questions. Please let me know if you still have inquiries.

Kamen

[binuacs · Accepted solution]

@KamenRider I updated the workflow based on the condition if the the absence end data and absence start date are same then keep the same records in output , else use the date range and update the time. let me know that works for you