Timesheet App

[Sinethemba_Gayi]

Hi, My Regular Hours column or App Formula displays Total Hours with a - if timezones are between AM and Pm or over 8 Hours: Examples belows
Total Hours

-6:43

-16646224:00

Overtime Hours

-16646222:44

How can I rectify this to give me something like Total Hours = 10:00 or 15:00

[LeventK]

@Sinethemba_Gayiza
Is your start and end time columns are DATETIME type?

[Sinethemba_Gayi]

No, they are “Time” type

[Sinethemba_Gayi]

[LeventK]

@Sinethemba_Gayiza
So according to this timesheet, the total hour shall be calculated as around 17hrs. Am I right?
If that’s the case, you should first change your time column to DATETIME, so that we exactly now the start datetime and end datetime. Than it’s easy to calculate.

From your example it seems that the work had started 12:55pm today and ended 6:12am in the morning today (or may be it’s the next day, who knows). Therefore AppSheet cannot correctly define the start and end resulting in negative hours.

Actutally, as AppSheet is based on AJAX code, there is no TIME in AJAX, JAVA and JavaScript. All you have is DATETIME only and DATE and/or TIME values are jointly calculated against EPOCH as per ISO8601 JODA TIME. So the Time In value you see is considered as 1/1/1970 12:55:53 PM and Time Out value you see is considered as 1/1/1970 06:12:00 AM and results in negative value.

[Sinethemba_Gayi]

Should I change the Total Hours column only or Time In and Time Out as well?

[LeventK]

@Sinethemba_Gayiza
[Time In] and [Time Out] columns shall be DATETIME, and [Total Hours] column shall be DURATION

[Sinethemba_Gayi]

Thanks, I get 17:16:07 Total Hours.

I have a Google Sheet for Regular Hours, Overtimes that still gives me “-16646224:44”, and that formula for Regular Hours is =ARRAYFORMULA(IF(LEN(E2:E), IF(TIMEVALUE(E2:E)>0.3333333333, TEXT(0.3333333333, “hh:mm”),E2:E),))

and for Overtime Hours =ARRAYFORMULA(IF(LEN(E2:E), IF(TIMEVALUE(E2:E)>0.3333333333, TEXT(E2:E-0.3333333333, “hh:mm”), 0),))

[LeventK]

@Sinethemba_Gayiza
Why do you need to calculate the regular hours and overtimes with Sheet Formulas like ARRAYFORMULA? It’s an expensive expression and increases the app’s sync time. Why don’t you calculate them with your app?

[Sinethemba_Gayi]

I don’t know how to calculate it using the App. It only has regular hours and I could only get assistance with Google Sheet formula.

Desirable, I want to calculate overtime and overtime pay. I can share with you my Google Spreadsheet.

Kind Regards

Sinethemba Gayiza
Cell: 073 543 4118
Fax: 086 275 4572
www.gayiza.wordpress.com

AN ACKNOWLEDGEMENT OF RECEIPT WILL BE APPRECIATED!!!

This email is intended only for the person to whom it is address and/or otherwise authorized personnel. The information contained herein and attached is confidential and the property of SINETHEMBA GAYIZA. If you are not the intended recipient, please be advised that viewing this message and any attachments, as well as copying, forwarding, printing, and disseminating any information related to this email is prohibited, and that you should not take any action based on the content of this email and any attachment. Please note that the views and opinions expressed herein are solely those of the author and do not necessarily reflect those of the company. While antivirus protection tools have been employed, you should check this email and attachments for the presence of viruses. No warranties or assurances are made in relation to the safety and content of this email and attachments. SINETHEMBA GAYIZA accepts no liability for any damage caused by any virus transmitted by or contained in this email and attachments. no liability is accepted for any consequences arising from this email.