Skip to main content

Notifications

Power Automate - Building Flows
Answered

Issue with DateTime columns when pulling from Excel spreadsheet using Power Apps

Posted on 25 Nov 2024 22:52:30 by 13
Hello Power Community,
 
I am seeking assistance understanding how to resolve DateTimes when pulling them into another Power App. Currently I have a Spreadsheet holding some check in/out times displayed in 24h time. 
 
However when I pull it out into a Data Table in a Power App it displays as completely different time period. I've ensured that the formatted cells are Time and when I change it to text it just becomes decimals numbers.
The code for the text is Text(ThisItem.'Check In Time',DateTimeFormat.LongDateTime) (Displayed long here for troubleshooting but ShortTime otherwise)

Additionally I need the case where there is no check out time (A client hasn't checked out yet). However it shows up as error.

The current code for text in this box is this: If(IsBlank(ThisItem.'Check Out Time') , " ",Text(ThisItem.'Check Out Time'))
Are Excel's values not considered blank? If not, what is it?

Any assistance is appreciated. Thank you in advance.
  • Chriddle Profile Picture
    Chriddle 6,991 on 26 Nov 2024 at 17:18:57
    Issue with DateTime columns when pulling from Excel spreadsheet using Power Apps
    Set the cell format in Excel to "text", then insert your time strings.
    After that, you get the cell values as strings.
  • HarZ Profile Picture
    HarZ 13 on 26 Nov 2024 at 17:03:06
    Issue with DateTime columns when pulling from Excel spreadsheet using Power Apps
    Thank you for the informative response! Didn't know it was such a weird case of Excel. What do you recommend in place of it? Additionally, would it be possible to change the export type into this Excel spreadsheet to be a String instead of DateTime? I don't need datetimes as I'm not manipulating any data after its been entered. As I've mentioned converting the Excel table itself just creates decimal numbers. 
  • Verified answer
    Chriddle Profile Picture
    Chriddle 6,991 on 26 Nov 2024 at 10:31:04
    Issue with DateTime columns when pulling from Excel spreadsheet using Power Apps
    December 30, 1899 is day 0 in Excel. Since your Excel cell does not provide a date, you will receive this one.
     
    Maybe you get the wrong time because Excel doesn't handle time zones and Power Automate assumes UTC.
    This UTC time seems to be calculated to your current timezone (are you UTC-8, e.g. California?).
    If so, you'll need to calculate the correct UTC time from this mess before inserting it into Dataverse.
     
    You should not insert a space if a datetime is expected.
     
    Conclusion: Don't use Excel, especially if you have to work with dates and times ;)
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,129

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,797

Leaderboard