# Formula for calculating days from date range

edited 09/04/24

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.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Are you able to provide some screenshots for context?

• 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.

• ✭✭✭✭✭✭

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

• edited 09/05/24

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..

• ✭✭✭✭✭✭

Happy to help. 👍️

The above should do that.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!