Date formula is not working

[lolla70]

Hi All

I created a Sharepoint List that include "Check In Date" and "Next Check In Date"

I have a button in the App on "TEXT" I wrote 

If(IsBlank(LookUp('Back to Work Eligibility', Employee.Email=VarUserEmail)),
"Check in Required",
"Next Check-In Date " & Text(DateTimeValue(Text(First('Back to Work Eligibility'). 'Next Check-In Date' )), "[$-en-US]mm/dd/yyyy"))

On other button the Patch

Patch(
'Back to Work Eligibility',
Defaults('Back to Work Eligibility'),
{
Title: "Status from " & User().FullName & " on " & Now(),
'Check-In Date': Now(),
'Eligibility Status': EmployeeEmail,
Result: lblResult.Text,
'POC Name': ThisItem.'Employee Name'.DisplayName,
'Next Check-In Date': DateAdd(
Now(),
Value(varCheckInDays),
Days
),

The button is not updated the Next Date Button on the APP

When I change the formula to "Last" instead of First it works but then all my colors change to black. Cause I need to get the last Next Check - In Date. In the SharePoint it works but it is not updating in the App????

If(IsBlank(LookUp('Back to Work Eligibility', Employee.Email=VarUserEmail)),
"Check in Required",
"Next Check-In Date " & Text(DateTimeValue(Text(Last('Back to Work Eligibility'). 'Next Check-In Date' )), "[$-en-US]mm/dd/yyyy"))

Any idea??

On other button the Patch

[lolla70]

It is correct, however my problem now

If I put the mouse in the first DateAdd the result is 1/28/2021, 5:00:00 AM

The Second DateAdd 1/28/2021, 12:00:0 AM

Today() 1/27/2021, 12:00:00AM

[RandyHayes · Accepted solution]

@lolla70 

That is not a problem, that is your date in UTC.  If you write that to the list it will be the UTC time of the date.  When you pull the record back in to display, you can adjust the UTC with the following:

With({_email: User().Email},

    If(
        !LookUp('Back to Work Eligibility', StartsWith(Employee.Email, _email), true),
            "Check in Required",
        
            "Next Check-In Date " & 
                With({_records:
                         SortByColumns(
                            Filter('Back to Work Eligibility',
                                StartsWith(Employee.Email, _email)
                            ),
                            'Next Check-In Date',
                            Descending
                         )},
                    Text(
                        With({_utc:First(_records).'Next Check-In Date'},
                           DateAdd(_utc, -TimeZoneOffSet(_utc), Minutes)
                        ),
                        ShortDate
                    )
                )
    )
)

[lolla70]

Again and again you are a life saver. Thank you so much

[lolla70]

Hi @RandyHayes 

Quick question, I was seeing in my phone the User Photo and now I don't see it anymore 

User().Image

I have connection - Office365Users 

I am not sure what is happening, I see the image in the desktop but not my phone. Any idea?

[RandyHayes]

@lolla70 

User() and Office365 are two different things.  I would use the Office365 connector to get the photo rather than the User() function.

[lolla70]

I put this formula and I got an error

Worked in the computer but not the phone.

Office365Users.UserPhotoV2(User().Email)

[RandyHayes]

@lolla70 

You have a blocking policy to odata information on your tenant. 

Please take a look over this article on how you can remove that.

[lolla70]

Hi @RandyHayes 

Can you help me with this formula?

You solved this for me but wants to add one more thing to it and it is not working!.

In the Check in Required Button OnText Property Code I have the following:

With(
{_email: User().Email},
If(
!LookUp(
'Back to Work Eligibility',
StartsWith(
Employee.Email,
_email
),
true
),
"Check in Required",
"Next Check-In Date " & With(
{
_records: SortByColumns(
Filter(
'Back to Work Eligibility',
StartsWith(
Employee.Email,
_email
)
),
"NextCheck_x002d_InDate",
Descending
)
},
Text(
With(
{_utc: First(_records).'Next Check-In Date'},
DateAdd(
_utc,
TimeZoneOffset(_utc),
Minutes
)
),
ShortDate
)
)
)
)

Now I want to add to it : 

If(lblResult_1.Text = "Work from Home", DateAdd(Today(), 1, Days)) !!!!

because if the "Work from Home" is the result in the main screen the employee will not confirm and post in the sharePoint, So I want to show the next Day check-in .. 

[RandyHayes · Accepted solution]

@lolla70 

I'm not entirely sure I understand where you want to add this.  

Are you stating the the Text would be either "Check In Required", "Next Check-In Date <dateAsDeterminedByFormula>", or "Next Check-In Date <dateAsDeterminedByWorkAtHomeLabel>"??

If that is the case, then your formula would be this:

With({_email: User().Email},
    If(!LookUp('Back to Work Eligibility', StartsWith(Employee.Email,_email),true),
        "Check in Required",

        "Next Check-In Date " & 
        
        If(lblResult_1.Text = "Work from Home", 
            DateAdd(Today(), 1, Days),
        
            With({_records: SortByColumns(Filter('Back to Work Eligibility',StartsWith(Employee.Email,_email)), "NextCheck_x002d_InDate", Descending)},
                Text(
                    With({_utc: First(_records).'Next Check-In Date'}, 
                        DateAdd(_utc, TimeZoneOffset(_utc), Minutes)
                    ), 
                    ShortDate
                )
            )
        )
    )
)

[lolla70]

I want if the Result label is "Work from home" to show the next check-in Date. and if the result is different it need to show the next Date-in after they click the button confirmation. 

The Work from Home result employee doesn't need to click the confirmation button to show the Next Check-In Date. However the other result need to click the confirmation button. So I want to add if the Result is Working from home to show the Next Date .. ohhh I think I confused you now

When I put this code. The 

With({_email: User().Email},
If(lblResult_1.Text = "Work from Home", DateAdd(Today(), 1, Days) &
If(
!LookUp('Back to Work Eligibility', StartsWith(Employee.Email, _email), true),
"Check in Required",

"Next Check-In Date " &
With({_records:
SortByColumns(
Filter('Back to Work Eligibility',
StartsWith(Employee.Email, _email)
),
"NextCheck_x002d_InDate",
Descending
)},
Text(
With({_utc:First(_records).'Next Check-In Date'},
DateAdd(_utc, TimeZoneOffset(_utc), Minutes)
),
ShortDate
)
)
)
)
)