DateTime Formula returns 12/30/1899

[Sinethemba_Gayi]

I am working on the Timesheet App and have been advised that to in order to achieve calculation of Overtime Hours, I need to set Time In and Time Out to DateTime formula.

Using the Now() initial value, this functions optimally in Time In but when a User Clocks Out and initiate Time Out the recorded date is “12/30/1899”

I believe this is because the Initial value cannot be set to Now()

How best can I mitigate this issue?

[Aleksi]

If your column type in sheet is DateTime and same with the column type in the app, the NOW() is the correct expression into your initial value field.

[Sinethemba_Gayi]

If NOW() is set on TimeOut then App captures the same data in Time In and Time Out, basically the user is not given option to Clock Out.

[LeventK]

@Sinethemba_Gayiza
Provided you are using action buttons for Clock In and Clock Out, then you shouldn’t be using any initial values already.

[Lynn]

oops

[LeventK]

@Lynn
@Sinethemba_Gayiza needs Datetime, not just time because he needs to calculate some overtime hours. What happens if the user clocks out the next day? You can never know that with only time value.

[Lynn]

Sorry just showing actions, will delete. Dont want to confuse anybody.

[LeventK]

@Sinethemba_Gayiza
I believe this is the 3rd or the 4th time you are asking the same question, so let me try to explain once more:
For Datetime type columns, the initial value is always NOW() and just for this moment it’s calculated as 5/9/2019 12:37:23 am

For Time type columns, the initial value is always TIMENOW() and just for this moment it’s calculated as 12/30/1899 12:37:23am. For Time type columns, a date of 12/30/1899 is automatically appended (which is called Joda Date) because in Unix systems there is no time value, there is only datetime value and it’s concatenation of date & time basically. And this is by-default and by-design like this.

For Date type columns, the initial value is always TODAY() and just for this moment it’s calculated as 5/9/2019 12:00:00 am. For Date type columns, a time of 12:00:00am is automatically appended (which is called Joda Time) because in Unix systems there is no date value, there is only datetime value and it’s concatenation of date & time basically. And this is by-default and by-design like this.

For Duration type columns, there is no initial value expression like above, because it’s a calculation column of 2 date, time or datetime values.

In general; for Time In and Time Out or Clock In and Clock Out, it will be essential if the column types are set as Datetime BOTH in the gSheet and the app’s column structure, as the calculation will be more specific and clear. Because, you cannot now to which specific date that entered time value belongs to.

Let’s have a sheet like this and let’s do some basic Math:

---

Option#1

---

Basics: Concatenate [Date] & [Time] columns and find the duration between this concatenation and [DateTime] column.

DATETIME(CONCATENATE([Date]," ",[Time])) - [DateTime]

OR

DATETIME([Date]&" "&[Time]) - [DateTime]

Here is how AppSheet interprets this expression and makes the calculation. The result is 24:00:00 or 1.00:00:00. Notice how the [Date] and [Time] column values are evaluated.

---

Option#2

---

Basics: Find the difference between [Datetime] and [Date] columns as duration

[Date] - DATE([DateTime])

Here is how AppSheet interprets this expression and makes the calculation. The result is 24:00:00 or 1.00:00:00. Notice how the [Date] and [Time] column values are evaluated.

---

Option#3

---

Basics: Find the difference between [Time] and [Datetime] columns as duration

[Time] - TIME([DateTime])

Here is how AppSheet interprets this expression and makes the calculation. The result is 00:00:00 or 0.00:00:00. Notice how the [Date] and [Time] column values are evaluated.

Hope it’s clear now

[Sinethemba_Gayi]

I am trying to get my way around this @LeventK, on the gSheet both Time In and Time Out are set to DateTime formula but I still get the 12/30/1899 date

[LeventK]

@Sinethemba_Gayiza
Can you please share your timesheet app with levent@able3ventures.com email as a co-author and the gSheet with edit access pls? This way it will be a lot more easier.

[Sinethemba_Gayi]

Thanks @LeventK , as I had mentioned previously, I had deleted the first App and copied again. On this copy I am yet to create Overtime Columns because I wanted to first solve the DateTime issue

[LeventK]

@Sinethemba_Gayiza
I have fixed it. The reason of 12/30/1899 date is because you are trying to record a TIMENOW() value to a DATETIME type column with the Clock Out action.

[Sinethemba_Gayi]

Thank you so much @LeventK, going forward I will just copy this same App

[LeventK]

You’re welcome. Please remove the sharings.

[Sinethemba_Gayi]

I get these errors when I input App Formulas for Overtime columns:

1. IFS(
   HOUR([Total Hours]) >= 8,“08:00:00”,
   TRUE,[Total Hours]
   )

Error message: IFS function is used incorrectly:Inputs to IFS() must be condition-value pairs. Input 4 is not a value of a consistent type.

2. (HOUR([Overtime Hours]) + MINUTE([Overtime Hours]) / 60 + SECOND([Overtime Hours]) / 3600) * [Overtime Pay]

Error message: Parameter 1 of function HOUR is of the wrong type

Also, how do I have the above Overtime Wage calculation to Calculate normal wage at Normal rate X 1.5?

[LeventK]

@Sinethemba_Gayiza
Check the column types for [Total Hours] and [Overtime Hours]

Expression in #2 produces a Number not a Duration

[Sinethemba_Gayi]

They are all on Duration. My main concern is the first question.

IFS(
HOUR([Total Hours]) >= 8,“08:00:00”,
TRUE,[Total Hours]
)

IFS function is used incorrectly:Inputs to IFS() must be condition-value pairs. Input 4 is not a value of a consistent type.

[Steve]

"08:00:00" is a Time, "008:00:00" is a Duration (believe it or not).

[Sinethemba_Gayi]

So @Steve you advise I add another 0 before 8?

[Steve]

Sure, give it a try.

[Sinethemba_Gayi]

Thank you so much @Steve, it worked