Changing from Date to DateTime Formula

[Gordon_Peery]

I have an employee time log database where each employee signs in with a timestamp date field (they can adjust it if they need to). Then they log out, and an additional field calculates their hours: DATETIME_DIFF({Time out}, {Time in}, ‘minutes’)/60

This is working fine, but we’ve had a request to have the date display the day of the week, so instead of showing 1/11/18 9:30 AM, it would display Th 1/11/18 9:30 AM.
It is my understanding from reading other forum articles that first I would need to change the field from a DATE field to a Forumula field, and then do something like this:
DATETIME_FORMAT(NOW(DD-dd-yy ‘hh:mm-’))

I’ve tried various permutations of this but keep getting an error, so I’d like advice on the correct formula, but then would this impact the formula I referenced above that calculates the hours and minutes worked?

Thank you.

Otherwise, this database has been a great resource for a small business situation that previously relied on hand-written time sheets. I’d be happy to share it as a template.

[Andre_Zijlstra]

This is the formula I checked:
DATETIME_FORMAT(Date_Field, ‘dd DD/MM/YYYY hh:mm A’)

Should work.

[Gordon_Peery]

This did not work:

[W_Vann_Hall]

Check the single quotes surrounding the format specifier. When you copy/pasted @Andre_Zijlstra’s formula, you inadvertently picked up ‘pretty formatted’ matching inverted commas (‘’) instead of the required straight single quotes (''). Simply overwrite them with the single-quote character, which typically is the unshifted double-quote (") key found between the colon/semi-colon (:;) and Enter keys.

[Gordon_Peery]

The single quotes issue has been addressed, however there is still a problem.
In my original field, I simply used “Date” for the field type, It put in the current date / time, but then the user had the option to modify it, which is what I want.

This formula requires referencing another field. In the example provided this was Date_Field. So, I actually made this field (using the date format). The formula does not produce a red Error message (as above) , however in the Time In field displays “ERROR!” I also tried referencing a field that I have called the record creation date. That did produce the desired information however (a) it is not modifiable and (b) it is five hours later than the actual time stamp.

I feel like we’re getting close, and I appreciate your patience and advice.

Gordon

[W_Vann_Hall]

Ah. I probably should have read the entire thread. :winking_face:

Unfortunately, if you convert {Time In} and {Time Out} into Formula fields, you will no longer be able to use them for data entry. And, as you’ve found, none of the options for formatting a Date field include the day of the week.

Assuming your employees are entering these times in a Grid view (which seems a pretty safe assumption, now I think about it), what I would do is to create two new fields either directly before or directly after {Time In} and {Time Out}. Name them something like {.} (yes, that’s simply a period) and {!} so you can make the columns as narrow as possible without the obstructed field name becoming distracting. Define them as Formula fields with the following formula:

IF({Time In}=BLANK(),BLANK(),DATETIME_FORMAT({Time In},'ddd'))

(Obviously, the one flanking {Time Out} should reference that field.)

Now, until the employee has entered a DateTime into the input field, {.} will remain blank. After the date is entered, it will display a three-letter abbreviation for the day of the week. (Use 'dd' for a two-letter abbreviation or 'dddd' for the name in full.)

It’s not quite as elegant as a fully formattable Date field would be, but it gives you what you need with a minimum of fuss.

[Gordon_Peery]

Thank you - success!