Counting of dates in between given two dates, those are only finding in database

Hi Excel Experts,

We have a database of attendance report, however, considering multiple in & out we require data base in desired format.

Into attached file there are 2 different worksheets i.e. 1. Available database & 2. Desired worksheet

From Available database, we are require fields in following manner in summarized way..

PERSONNUM | In | Out | Report date from | Report date To | No. of Shifts attended (in between to & from dates) | Details
(I have prepared manual workout into Desired worksheet, however, column F and G is difficult calculate manually hence, only 1 record is calculated)
For Example..
From To No. of Shifts attended (in between to & from dates)
21-Feb-2018 13-Mar-2018 19 (Calculated dates in between these two dates, those are only finding in Available database)

Please help me to resolve the issue.

Kravindra

No attachment.

sorry
attached now

Number of records in your desired result confuses.

Please Login or Register  to view this content.

Hi jindon,
Thanks a lot for your prompt response.
However, the attachment in thread 4 is not opening , can you PLEASE open the said file at your end and resend to me with proper extension.

Kravindra

I just did and I can download and open it without problem.

So sorry Boss,
I have tried multiple times and different wasy also but, unable to open the said file
please ref. the attached snaps for same

Kravindra

Strange, it is the file that you attached in #3.
Saved as xlsm this time.

HI jindon,

As I was busy with some another task, really sorry for late response.

Thanks for your very prompt and almost 90% problem solving response in a single reply. Once again thanks for the same

I have check the file, now it is working and it seems almost 90.0% OK as expected.

- Only single bug / step is not considered anywhere; actually I missed the same in description in previous threads in this post and that is as follows;

We require one more column after "Details" Column i.e. is Missed Punches (wherever find blank IN or OUT punches)..
Missed IN Punch > Feb 18 : 21,22 & Mar 18: NIL.
Missed OUT Punch > Feb NIL & Mar 18: 13.


Kravindra





Thanks a lot for your commendable efforts; you are really champ !!!

Kravindra

We require one more column after "Details" Column i.e. is Missed Punches (wherever find blank IN or OUT punches)..
Missed IN Punch > Feb 18 : 21,22 & Mar 18: NIL.
Missed OUT Punch > Feb 18 : NIL & Mar 18: 13.

Upload a workbook with EXACT desired result. Do not be lazy.

Hi Jindon,
Please ref. attached file
(requirement is high-lighted in yellow colour column; done only for single row)
Sorry for convenience caused to you.
Kravindra

You said

Originally Posted by RavindraK

I have check the file, now it is working and it seems almost 90.0% OK as expected.

And now, again

Originally Posted by RavindraK

(requirement is high-lighted in yellow colour column; done only for single row)

Complete all the lines this time. I don't want to do a guess work anymore.

Boss,
During problem described by me in first thread of this post, I missed a issue as follows;
We require one more column after "Details" Column i.e. is Missed Punches (wherever find blank IN or OUT punches)..
Missed IN Punch > Feb 18 : 21,22 & Mar 18: NIL.
Missed OUT Punch > Feb 18 : NIL & Mar 18: 13.
, extremely sorry for the same

And considering above issue, you solved my solved my problem which is approx. 90% , remaining 10% is for the logical requirement, which I was missed out to describe.

If you consider my request, then all my requirement will be fulfilled, including the logical requirement, which I missed out to describe in 1st thread.

Thanks a lot for your commendable efforts and Sorry for convenience caused to you due to additional logic request by me.

Please help me.

kravindra

Hi Jindon,
Thanks a lot for your commendable efforts and Sorry for convenience caused to you due to additional logic request by me.

Please help me to resolve the same.

kravindra

Hi Jindon,

Please help me to resolve the same.

kravindra

Originally Posted by jindon

Complete all the lines this time. I don't want to do a guess work anymore.

No effort, no return.

Hi Jindon,
Extremely sorry for additional request from my side after your code workout.

This time all the lines are completed
please help me to resolve the same.

Kravindra

Please help me to resolve the said query.
Kravindra

Dear Excel Master,
Plase help me to resolve the said query.
Kravindra

Originally Posted by RavindraK

This time all the lines are completed

Do you really understand what I said?
Where is the COMPLETED workbook?

I will be busy rest of the day anyway.

Sir,
Please ref. attached file
(requirement is high-lighted in yellow colour column in Desired worksheet; done only for single row)
Sorry for convenience caused to you. Please do it for me.
Kravindra

So sorry, but I never want to do a guess work again for this particular thread.

I said "COMPLETE" all rows and I need to see them all to test the result, so that you can't say "90%" or something like that.

You need to wait for someone else to come in.

OK Sir,

Complete requirement is as follows;

We have a database of attendance report, however, considering multiple in & out we require data base in desired format.

Into attached file there are 2 different worksheets i.e. 1. Available database & 2. Desired worksheet

From Available database, we are require fields in following manner in summarized way..

PERSONNUM | In | Out | Report date from | Report date To | No. of Shifts attended (in between to & from dates) | Details
(I have prepared manual workout into Desired worksheet, however, column F and G is difficult calculate manually hence, only 1 record is calculated)
For Example..

1. Colum named "Details" Column (From To No. of Shifts attended (in between to & from dates))
21-Feb-2018 13-Mar-2018 19 (Calculated dates in between these two dates, those are only finding in Available database)

2. We require one more column after "Details" Column i.e. is Missed Punches (wherever find blank IN or OUT punches)..

For example ..

for PERSONNUM : BIS1234

desired result/columns in "Desired worksheet"
PERSONNUM = BIS1234
In = 00-01-1900 00:00
Out = 13-03-2018 11:23:00
Report date from = 21-Feb-2018
Report date to = 13-Mar-2018
No. of Shifts attended (in between to & from dates) = 19
Details = Feb'18 : 21,22,23,24,25,27,28 & Mar'18 ; 1,2,3,5,6,7,8,9,10,11,12,13
Missed Punches = Missed IN Punch > Feb 18 : 21,22 & Mar 18: NIL.
Missed OUT Punch > Feb 18 : NIL & Mar 18: 13.
===
All desired columns (EXCLUDING "Missed Punches") successfully completed / done by jindon

PLEASE HELP ME TO RESOLVE THE SAME

Kravindra

Good luck.

Dear Excel Experts

PLEASE HELP ME TO RESOLVE THE SAME i.e. Remaining part as follows..

Missed Punches =
Missed IN Punch > Feb 18 : 21,22 & Mar 18: NIL.
Missed OUT Punch > Feb 18 : NIL & Mar 18: 13.

Thanks in advance.

Kravindra

Dear Excel Masters,
Plase help me to resolve the said query.
Kravindra

Thanks JeteMc for your response,
However, due to our report limitation, we require the Missed Punches details as requested, you tried very well but we are unable to use multiple columns ; we require the Missed Punches details in single cell in front of each employee as requested .
Further, once again thanks to jindon, who has helped me greatly to resolve desired columns, now pending part is only "Missed Punches", what I have requested later.

Dear Excel Masters,

Plase help me to resolve the balance part of query i.e Missed Punches =

Kravindra

Dear Excel Masters,

Plase help me to resolve the balance part of query i.e Missed Punches =

Kravindra

Can any Excel Master, Plase help me to resolve the same

Kravindra

Can any Excel Masters, Please help me to resolve the same.

Kravindra

Sir,
Thanks for your intervention, however, my requirement is as follows;
2. We require one more column after "Details" Column i.e. is Missed Punches (wherever find blank IN or OUT punches)..

For example ..

for PERSONNUM : BIS1234

desired result/columns in "Desired worksheet"
PERSONNUM = BIS1234
In = 00-01-1900 00:00
Out = 13-03-2018 11:23:00
Report date from = 21-Feb-2018
Report date to = 13-Mar-2018
No. of Shifts attended (in between to & from dates) = 19
Details = Feb'18 : 21,22,23,24,25,27,28 & Mar'18 ; 1,2,3,5,6,7,8,9,10,11,12,13
Missed Punches = Missed IN Punch > Feb 18 : 21,22 & Mar 18: NIL.
Missed OUT Punch > Feb 18 : NIL & Mar 18: 13.

Please ref. attached file for details (requirement Colum is H named "Missed Punches" in "Desired worksheet"
), highlighted in yellow colour, I have done manually for one person i.e. BIS1234, and I m looking same results to all in column H.

Thanks in advance.

Kravindra

xladept,

EXCELLENT !! Many thanks it works a treat

Many thanks for all your time effort it is very much appreciated.

Also thanks to Jindon, who creates macro for Shifts attended details initially.

Regards

Kravindra