Based on Time bot write Present or Absent

Hi…

I have Excel Sheet in that excel sheet no. of employees list in the list In Time and Out time mension based on that i have mension the if more 8 hrs wrked write “Working Hours + OT” and anothe column “Present”. If the cell is empty or null write as “Absent”

Thanks
Shyam

Hi @Shyam_Pragash ,

Sample data along with a short example of the output you want would be most appreciated as it would put us in a much better position to assist you.

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok

As per your requiremnt attached the sample employee details … There is some column is empty and some columns are filled…

Attendance.xlsx (8.1 KB)

i want to overcome this issue…

Thanks
Shyam

Hi…

Any Updates ??

Still i am waiting…

Thanks
Shyam

Hi @Shyam_Pragash

Have look into the Thread

Regards
Gokul

Hi @Shyam_Pragash ,

Sorry for the late response, could you check if the operations are as expected?

(From row In dt_sampleData.AsEnumerable()
Let chkEmpty = String.Join("",row.ItemArray.Skip(2))
Let attendance = If(String.IsNullOrEmpty(chkEmpty.Trim),	"Absent","Present")
Let wrkHrs = If(attendance.Equals("Present"),
If(Math.Abs((Convert.ToDateTime(row("Work End Time").ToString) - Convert.ToDateTime(row("Work Start Time").ToString)).Hours)>8,
	"Worked for "+Math.Abs((Convert.ToDateTime(row("Work End Time").ToString) - Convert.ToDateTime(row("Work Start Time").ToString)).Hours).ToString +" Hours",
	"Worked less than 8 Hours"),"")
Let ra = New Object(){
	row("Name of Employee"),
	attendance,
	If(IsNothing(row("SSFA_LoginTime")) OrElse String.IsNullOrEmpty(row("SSFA_LoginTime").ToString),"",Convert.ToDateTime(row("SSFA_LoginTime").ToString).ToString("h:mm:ss tt")),
	If(IsNothing(row("SSFA_LoginTime")) OrElse String.IsNullOrEmpty(row("SSFA_LoginTime").ToString),"",Convert.ToDateTime(row("Work Start Time").ToString).ToString("h:mm:ss tt")),
	If(IsNothing(row("SSFA_LoginTime")) OrElse String.IsNullOrEmpty(row("SSFA_LoginTime").ToString),"",Convert.ToDateTime(row("Work End Time").ToString).ToString("h:mm:ss tt")),
	wrkHrs}
Select dt_result.Rows.Add(ra)).CopyToDataTable()

UpdateEmployeeStatus.xaml (9.7 KB)

Kind Regards,
Ashwin A.K

2 Likes

Hi @ashwin.ashok

Thanks for your Reply…

can you tell how to fill the data if cell is Absent fill the value 00:00:00 remaining columns

Thanks
Shyam

Hi @Shyam_Pragash ,

What do you want the Working Hours columns to hold for Absent?

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok

in that attendance column is Absent remaining columns

image

Like This.

Hi @Shyam_Pragash ,

Is this fine?

(From row In dt_sampleData.AsEnumerable()
Let chkEmpty = String.Join("",row.ItemArray.Skip(2))
Let attendance = If(String.IsNullOrEmpty(chkEmpty.Trim),	"Absent","Present")
Let wrkHrs = If(attendance.Equals("Present"),
If(Math.Abs((Convert.ToDateTime(row("Work End Time").ToString) - Convert.ToDateTime(row("Work Start Time").ToString)).Hours)>8,
	"Worked for "+Math.Abs((Convert.ToDateTime(row("Work End Time").ToString) - Convert.ToDateTime(row("Work Start Time").ToString)).Hours).ToString +" Hours",
	"Worked less than 8 Hours"),"00:00:00")
Let ra = New Object(){
	row("Name of Employee"),
	attendance,
	If(IsNothing(row("SSFA_LoginTime")) OrElse String.IsNullOrEmpty(row("SSFA_LoginTime").ToString),"00:00:00",Convert.ToDateTime(row("SSFA_LoginTime").ToString).ToString("h:mm:ss tt")),
	If(IsNothing(row("Work Start Time")) OrElse String.IsNullOrEmpty(row("Work Start Time").ToString),"00:00:00",Convert.ToDateTime(row("Work Start Time").ToString).ToString("h:mm:ss tt")),
	If(IsNothing(row("Work End Time")) OrElse String.IsNullOrEmpty(row("Work End Time").ToString),"00:00:00",Convert.ToDateTime(row("Work End Time").ToString).ToString("h:mm:ss tt")),
	wrkHrs}
Select dt_result.Rows.Add(ra)).CopyToDataTable()

UpdateEmployeeStatus_v1.xaml (10.3 KB)

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok

Yes this is i excepted…

Thanks

Hi @ashwin.ashok

i have one doubt in this code.

I need one more correction on this code.

In Total Working Hours Column Leave the Value Present as it.

image

Just Fill the Empty Columns 00:00:00 if absent. yesterday you share one code …

image
Remove the text…

Thanks
Shyam

Hi @Shyam_Pragash ,

The images are not visible, also since this thread is closed could you please either open a new thread or message me.

image

Thank you.

Kind Regards
Ashwin A.K

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.