Multiple Condition

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

Sample Start & End time File.xlsx

Holiday Calendar.xlsx

Workflow

Accepted answers

All comments

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?

binu_acs

@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 : )

holiday.yxmd

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

Output File.xlsx

atcodedog05

Hi @sgaryali

Here is how you can do it.

Workflow:

Hope this helps : )

holiday.yxmd

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 : )

holiday.yxmd

sgaryali

Thank you for the help @atcodedog05

This is not what I am looking at we don't need to calculate Total Hours for dates other than matching Holiday file.

Like - 2022-01-02,2022-01-05, 2022-01-06..............

Dates which are common in both files those are to be taken into consideration then we have to check whether the date is falling on last of month if yes then (punch out - 24) otherwise (24- punch in).

Maybe I am not able to explain it anyways thank you so much for your help I will try some other way .

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703