Absent Date Ranges

NITIN MANE

New Member
Joined
Nov 27, 2017
Messages
2
I want to do something similar to this: https://www.mrexcel.com/forum/excel-questions/1002603-absent-dates-need-attendance-sheet.html

But i need one more help with same example.

if any one employee whose continuous absent then answer must be in format of " First Date To Last Date" in that period.

Emp. code01-Jan-1702-Jan-1703-Jan-1704-Jan-1705-Jan-1706-Jan-1707-Jan-1708-Jan-17absent dateAnswer Must be
Emp. 001AAAPPAPP01-01-2017, 02-01-2017, 03-01-2017, 06-01-201701-01-2017 TO 03-01-2017, 06-01-2017
Emp. 002PPPPPAAA06-01-2017, 07-01-2017, 08-01-201706-01-2017, 07-01-2017 to 08-01-2017

<tbody>
</tbody>
 
Last edited by a moderator:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
OK, creating a function like I did in the other one, here is a function that should work:
Code:
Function AbsentDays(myRange As Range, myRow As Long) As String

    Dim cell As Range
    Dim myString As String
    Dim chkAbs As Boolean
    Dim startDate As String
    Dim endDate As String
    
'   Initialize values
    chkAbs = False
    startDate = ""
    endDate = ""
    myString = ""
    
'   Loop through all the cells in the range
    For Each cell In myRange
'       Check for value
        Select Case cell
            Case "A"
                Select Case chkAbs
                    Case False
                        startDate = Format(Cells(myRow, cell.Column), "dd-mm-yyyy")
                        chkAbs = True
                    Case True
                        endDate = Format(Cells(myRow, cell.Column), "dd-mm-yyyy")
                End Select
            Case "P"
                Select Case chkAbs
                    Case True
                        If endDate = "" Then
                            myString = myString & startDate & ", "
                        Else
                            myString = myString & startDate & " to " & endDate & ", "
                        End If
                End Select
                startDate = ""
                endDate = ""
                chkAbs = False
        End Select
    Next cell
    
'   Check to see if last entry was absent and address that
    Select Case chkAbs
        Case True
            If endDate = "" Then
                myString = myString & startDate & ", "
            Else
                myString = myString & startDate & " to " & endDate & ", "
            End If
    End Select
    
'   Return string
    If Len(myString) > 0 Then
        AbsentDays = Left(myString, Len(myString) - 2)
    End If
    
End Function
Then you would use it like any other function in Excel.

The first argument in the function is looking for the range to check for the A’s and P’s for that particular row. And the second argument is looking for what row has the title row of dates (assuming the dates are entered as dates and not as text).
So, the formula would look something like:
Code:
=ABSENTDAYS(B2:I2,1)

 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,466
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top