Help to check day report of branch and mark with conditions – more “Day off”

[sbv1986]

Hi all
I have excel file name: check.slxm with sheet(result), another excel file name source.xlsx with sheet1
Now I want to have macro to check day of report of all branchs in Column(H) and Column(M) with conditions. Mark Y or N or L1 or L2 in file check sheet(result) with:
Y: Branch report on time
N: Branch not report yet
L1: Report later under 10 days than "Day off"
L2: Report later from 10 days and more.. than "Day off"
Day off: - Report code(G00014): Day 10 of next month
- Report code(G00854, C00264): Day 15 of next month
- Report code(G03654, F00024, A00014): Day 25 of next month
- Other Report code: Day 20 of next month
This code below work fine with me if “Day off” only day 25 of next moth, but now “Day off” split 4 group. Please help me one again.

Please Login or Register  to view this content.

Link to old thread: https://www.excelforum.com/excel-pro...ml#post5193277

[jindon]

My results are different from yours.

Please Login or Register  to view this content.

[sbv1986]

Thanks for your code jindon, I'll try soon

[sbv1986]

Thanks jindon but code work not as I want. The result like picture below:
Attachment 642032
For examble with file I attachment (month report 201906)
With row 99201001:
- Column G00014: need mark "Y" because dayreport 08-07-2019, "day off" 10-07-2019 => later 2 day
- Column G04156: need mark "L2" because dayreport 27-08-2019, "day off" 20-07-2019 => later 37 day
- Column G03266: need mark "L2" because dayreport 30-8-2019, "day off" 20-07-2019 => later 40 day

Could you help me one again, thanks./.

[jindon]

Y: Branch report on time

Doesn't this mean dayreport = day off ?

[sbv1986]

Doesn't this mean dayreport = day off ?

Hi Jindon, that's not the same day
Day report is the real day branch upload file => get value in column (M)
"Day off": the end day branch must be report
For examble: Day report of branch 99901001, code G00014 is 08-07-2019, "day off" 10-07-2019 => This branch report early 02 day so mark "Y".

Sorry with #4 with type mistake, this "later 2 day" => => early 2 day

[jindon]

OK, can you explain about "day off"?
I thought the calculation is simply date in Col.N - Col.M...

[sbv1986]

OK, can you explain about "day off"?
I thought the calculation is simply date in Col.N - Col.M...

Day report Only in column (M)

"Day off": the end of day branch must be report
There's 04 group "Day off" depend on 04 group code:
- Report code(G00014): "Day off" is day 10 of next month.
- Report code(G00854, C00264): "Day off" is day 15 of next month
- Report code(G03654, F00024, A00014): "Day off" is day 25 of next month
- Other Report code: "Day off" is day 20 of next month
For examble with code(G00014): If branch report 08-07-2019 mark "Y" (early), branch report 11-07-2019 mark "L1" (later 01 day with day_off), branch report 21-07-2019 mark "L2" (later 11 day with day-off)

[huuthang_bd]

I have edited the original code to suit with new rule.

Please Login or Register  to view this content.

[jindon]

Still confusing...

Report code(G00014): "Day off" is day 10 of next month.

If you mean NEXT month of col.M then all should be in time....
This is only my guess...

Please Login or Register  to view this content.

[sbv1986]

Thanks both huuthang_bd and Jindon.

Code of huuthang_bd work well.

[sbv1986]

Hi @Jindon

I've just check your code again and understand. Because I didn't copy all required of Old thread at: https://www.excelforum.com/excel-pro...ml#post5192729.

This's my mistake, sorry.
That's have another conditions if file name is the same (this mean branch report one code more than one time):

If file name is the same code will check number of report then choosen the row have max number. For examble:
G03144-99201001-01201001-201906-BI-M-01.xlsx and G03144-99201001-01201001-201906-BI-M-03.xlsx
=> Code check only G03144-99201001-01201001-201906-BI-M-03.xlsx

I'm very happy if you can improve your code again. Maybe your code is another way to do my work without huuthang_bd's code

[jindon]

OK, now I think I understand the logic how "dayoff" should be calculated. and the result is very similar(only one different to yours), which is

99201001
G00864
26-07-2019 18:36:40 and dayoff = 2019/7/20, it should be L1, instead of Y in D4.

Please Login or Register  to view this content.

[sbv1986]

Great code @Jindon, work well as I want and have the same result with @huuthang_bd's code

[jindon]

Glad it worked and thanks for then rep.

JFI, the results of mine and huuthang_bd's code are different here because of the use of DateSerial & DateValue.
DateValue depends on the regional setting.