Formula for calculating days from date range

Sagar SV

I have two sheets

Sheet A (contains only applied leaves, not all the dates range)

Column 1 - Leave Start Date, eg 01 jan 24

Column 2 - Leave End date, eg 03 jan 24

Column 3 - Employee code, eg ABC001

Sheet B (contains entire range of dates for particular year)

Column 1 - Date range (i.e. 01 jan24 , 02 jan24 , 03 jan24 , etc in once column till 31st dec24)

Column 2 - on leave (or) present - need formula for this

Header row of column2,3, etc - employee code

I need a formula for this for column 2 of sheet B where whenver an leave is being applied for the particular date range, it should show "On Leave" and for the rest of the days, where dates are not available in Sheet A, it should show "Present".

Your wonderful thoughts and ideas are highly appreciated.

P.s. I have tried multiple if-countifs loops and vlookups but that didnt work.

Thanks.

Paul Newcome

You would use a COUNTIFS along the lines of:

=IF(COUNTIFS({Sheet 1 Start Date Column}, @cell <= Date@row, {Sheet 1 End Date Column}, @cell >= Date@row, {Sheet 1 Employee Code Column}, @cell = "ABC001") > 0, "On Leave")

Paul Newcome

Are you able to provide some screenshots for context?

Sagar SV

Hi Paul,

Thanks for revert. Pls find below two screenshots for your reference.

Here in 2nd sheet (Attendance register i need formula for the entries such as "Present" or "On Leave", while taking the reference from sheet 1.

Paul Newcome

You would use a COUNTIFS along the lines of:

=IF(COUNTIFS({Sheet 1 Start Date Column}, @cell <= Date@row, {Sheet 1 End Date Column}, @cell >= Date@row, {Sheet 1 Employee Code Column}, @cell = "ABC001") > 0, "On Leave")

Sagar SV

Hi Paul,

Thanks for the formula. Worked like a charm. :D

Also Paul, may I request for an extension in the same formula where it will also capture the weekends too..

Paul Newcome

Happy to help. 👍️

The above should do that.