Answered

Importing hours from Excel is subtracting 37 minutes to each field

(0) Share

Report

Posted on by duartesilva

Hello,

I'm building an app in Power Apps which should display the timesheet of a worker. For this, I created an Excel file which has, for each period of the morning and afternoon, the respective starting and ending hour (e.g., starting hour of the morning - 8:00, ending hour of the morning - 12:00, starting hour of the afternoon - 13:00, ending hour of the afternoon - 17:00).

However, when I try to upload these data to Power Apps, each hour has always 37 minutes subtracted (for the example I've written above, there is 7:23, 11:23, 12:23 and 16:23).

Can you help me, please? In case you need to know, the formula I'm using to import the hour is "Text(LookUp(Colaboradores, Colaborador='ComboBox/Colaborador'.Selected.Colaborador, 'Hora Início Manhã'), ShortTime24)", where "LookUp" is being used to import the hours based on a combobox and "'Hora Início Manhã'" is "Starting hour of the morning" (the name of an Excel column where the data is stored).

Thank you,

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

v-qiaqi@microsoft.com on at

Like (1)

Report
Hi @duartesilva,

The reason why you have issue is that Power Apps displays locale time but Excel displays UTC time.

You need to supply the date of the Colaborador as a parameter inside the timezoneoffset() to get it to adjust correctly for daylight savings time.

Text(DateAdd((LookUp(Colaboradores, Colaborador='ComboBox/Colaborador'.Selected.Colaborador, 'Hora Início Manhã'),TimeZoneOffset((LookUp(Colaboradores, Colaborador='ComboBox/Colaborador'.Selected.Colaborador, 'Hora Início Manhã')),Minutes),DateTimeFormat.ShortTime24)

Was this reply helpful? Yes No
duartesilva 7 on at

Like (0)

Report

Hi, @v-qiaqi-msft ,

Many thanks for your quick and helpful reply. I implemented the code you suggested (with a small adjustment on the parenthesis). And, although the hour is now closer to the correct value, there is still a difference of 1 minute. For example, for a "starting hour of the morning" of "8:00", Power Apps now presents "7:59". Do you have any idea of how to correct this difference of 1 minute, please?

The code being used is: "Text(DateAdd((LookUp(Colaboradores, Colaborador='ComboBox/Colaborador'.Selected.Colaborador, 'Hora Início Manhã')),TimeZoneOffset((LookUp(Colaboradores, Colaborador='ComboBox/Colaborador'.Selected.Colaborador, 'Hora Início Manhã'))),Minutes), ShortTime24)".

Thank you,
DS

Was this reply helpful? Yes No
duartesilva 7 on at

Like (0)

Report

@v-qiaqi-msft ,

I've found how it can be erased this 1 minute difference. It just needs another "DateAdd" where it can be added this 1 minute. So, my final formula will be:

"Text(DateAdd(DateAdd((LookUp(Colaboradores, Colaborador='ComboBox/Colaborador'.Selected.Colaborador, 'Hora Início Manhã')),TimeZoneOffset((LookUp(Colaboradores, Colaborador='ComboBox/Colaborador'.Selected.Colaborador, 'Hora Início Manhã'))),Minutes), 1, Minutes), ShortTime24)".

Thank you very much for your help,
DS

Was this reply helpful? Yes No