Time Entry(Log-In/Out) Conditional Formula

[juhoneyighot]

Hello!

I have these two tables: 

First table shows the available time that a resource can work for a day.

Second Table is from the time entries with actual start and end time

What I want is the status for the actual start and end time with the following conditions:

1 If Actual start time is before the available time then "Early Log-in

2 If the Actual start time is between availabe time and 10min from available time (grace period) then "On-Time". If an resource starts on 7:06 is still "On time" since 7:06 is still in grace period.

3 If the Actual Start Time is beyond the grace period (Available time+10min) then "Late"

Same scenario with the status of Actual end time.

Thank you,

Juhoney

[v-zhengdxu-msft · Accepted solution]

Hi @juhoneyighot 

Thanks for the reply from @MNedix , please allow me to provide another insight:

You can use the Time(Hour('table'[column]),Minute('table'[column]),Second('table'[column])) to get the time from date/time column.

Then it seems that the relationship confused you, maybe you can try this:
First of all, I create a set of sample:

Next, the crossjoin() function can be useful to create a new table,
Click new table:

Table = 
	SELECTCOLUMNS(
		CROSSJOIN(
			'Available',
			'Actual'
		),
		'Available'[Resource],
		'Actual'[Date],
		'Actual'[Actual Start Time],
		"_Start_status",
		IF(
			DATEDIFF(
				'Available'[Available Start time],
				'Actual'[Actual Start Time],
				MINUTE
			) < 0,
			"Early Log_in",
			IF(
				DATEDIFF(
					'Available'[Available Start time],
					'Actual'[Actual Start Time],
					MINUTE
				) > 10,
				"Late",
				"On time"
			)
		),
		'Actual'[Actual End Time],
		"_End_status",
		IF(
			DATEDIFF(
				'Available'[Available End time],
				'Actual'[Actual End Time],
				MINUTE
			) < 0,
			"Early Log-out",
			IF(
				DATEDIFF(
					'Available'[Available End time],
					'Actual'[Actual End Time],
					MINUTE
				) > 10,
				"Late Log out",
				"On time"
			)
		)
	)

The sample is very simple that you can use selectcolumns() and calculatetable() function to choose what you need in your two original tables to mix as a new table.

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MNedix]

Yes, you can use Time = FORMAT([Date],"hh:mm:ss")

[juhoneyighot]

Hello

I've use this formula Time = FORMAT([Date],"hh:mm:ss") and get the Available and Actual Start Time See ss below. Is there a formula that I can only get the earliest time for each date? 

Second, I've tried the formula (using the Time format above) to get the Early log-in /late Log in etc however I bumped with this error.

DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

Hope you could still help me on this.

Thank you.

[MNedix]

How do you get multiple times after formatting the column? What changed in the data? You could probably try wrapping it in a FIRSTNONBLANK(Format...) For the second one, it might also be a formatting issue.

This is blowing a bit out of proportion, without looking at your data I cannot see where the problem is coming from. It works fine on my end.

[MNedix]

I had to recreate your table and I've added another person just for kicks. The way I did it is as follows:

1. Make sure that you have a relationship between the two tables

2 Create the following column

New Start Status = 
VAR _time = LOOKUPVALUE(Sheet1[Available Start Time],Sheet1[Resource],Sheet2[Resource])
var _delay = TIME(0,10,0)
RETURN
SWITCH(
    TRUE(),
    Sheet2[Actual Start Time]<_time,"Early Log-in",
    Sheet2[Actual Start Time]>_time+_delay,"Late",
    Sheet2[Actual Start Time]>_time ||  Sheet2[Actual Start Time]<=_time+_delay,"On time")

(As a rule of thumb, please make sure next time you upload actual data and not a screeshot).

Screenshots:

If this answered your question then please mark it as solution.

[juhoneyighot]

Hello!

Thank you. I tried using your formula and this is the result

I am not sure why 12:05,12:06,12:07 Start time has been flagged as Late Log-in. I am not sure if I have entered the correct formula you have given. This would be "On Time" since time in is part of a 10min grace period

Hope you can help me with this.

Thank you

[MNedix]

I assume you formatted the columns as Time, right? Can you attach the file? Also, do you have the relationship between 'bookableresource'[Name] and 'msdyn_timeentry'[Bookable Resource]?

[juhoneyighot]

Hello!

Yes it is formatted as time and I have relationship betwwen the 2 tables. I'm sorry but I can not attached the file since I am working in a cloud. Sharing files is restricted outside organization.

But I guess I know where is the problem. Data type source for _Available time and Start time is Date and Time 

I just change the type as time.

So if I have a STart time (which is a date and time format also) from time entry table after the set available time, it will have a Late Log-in.

Is there a way if I could get the time in each Start time and Available time without connecting it with the date? Any DAX formula that I could use to get the time?

Thank you