Multiple Condition

[sgaryali]

Hi

I want to calculate Total Hours

On conditions -

1) If date in Sample Start time & End time is matching with holiday file then I need to subtract (24 - Punch In) which will give 4 hrs

2) If date in holiday file is of previous day like we have 1/31/2022 and date in sample Start time & End time is of next day like we have 2/1/2022 then we need to subtract (24- Punch Out).

I know we have to use Join tool to take same date for first scenario but I am not sure what can we do second scenario?

Can anyone help please

[PhilipMannering]

If I understand correctly, you could join for the two datasets for the first scenario (as you pointed out).

For the second scenario, add a day to the holiday file before joining,

datetimeadd([date_field], 1, 'day')

Then join on this new field. Anything that joins -> subtract (24-Punch Out).

Would this work?

[sgaryali]

Hi,

This work partially as I am able to add date to let say 1/31/2022 which becomes 02/01/2022 and then I can use join to add the dates but it is also adding one date to 01/01/2022 which becomes 01/02/2022 and then I am getting 01/02/2022 also which is not a holiday date.


I hope was able to explain this clearly.

[sgaryali]

Hi,

Can anyone help please with this?

[binuacs]

@sgaryali Can you provide the expected output based on your input files?

[atcodedog05]

Hi @sgaryali 

I was able to map holidays to the next day (Nulls are not mapped to holidays) whats the next step? whats the expected output.

Hope this helps : )

[sgaryali]

Output Should Look Like something this

Sorry Just a little update

If holiday is falling on last of the month just like we have on 1/31/2022 then we will subtract (Punch out - 24)

For rest we will have same logic if date is matching then (24-Punch In)

Attaching the sample output file

[atcodedog05]

Hi @sgaryali 

Here is how you can do it.

Workflow:

Hope this helps : )

[sgaryali]

Hi @atcodedog05 

Thank you for your reply

You have taken only those dates which are matching with date in holiday file.

You have manually added the dates but in live data environment we will be having all types of dates which you can also check in the above File - Sample Start & End time File.

IMO we have to use join tool first then then use formula if the date is of last date of month then Punch out - 24 or else punch in - 24.

I am just not sure about the formula how to write it.

[atcodedog05]

Hi @sgaryali 

Sorry, I took that as input since it had output for validation.

Here is the workflow with the actual file just need to change the input.

Workflow:

Hope this helps : )