Dates and formulas question

[Tim_Sullivan]

Morning all. I have a base I’m working on that calculates overtime, It also compares the overtime shift worked to an employee’s scheduled shift. I have a set of DURATION fields for each employee - Mon Start, Mon End, Tues Start, Tues End… for all 7 days of the week. Based on the day of the week our OT shift works, it shows the appropriate day’s shift.

I’m pulling the start date of an event from one table (Overtime Events), the employee’s normal shift from another table (Employee Data), and the calculation is in a 3rd table (OT Shift Data).

I’m trying to concatenate the DATE ONLY from when an overtime shift begins, and the DURATION field from the employee’s info, to create one date field I can use for other calculations. None of the date/time formulas seem to give me the results I’m looking for.

Can anyone please offer any advice?

[Databaser]

Hi @Tim_Sullivan

Could you give a screenshot or something with the result you’re getting? What is the result that you’re looking for exactly?

[Tim_Sullivan]

So here’s a screenshot - I got this result using automation to paste the text values together in the same cell.:

I’m trying to make it look like: “12/29/2020 7:00 AM”

The automation result is so far the best I’ve gotten. I can’t seem to find an obvious way to convert date or duration fields to text first. If I could do that, I could concatenate them and then use DATETIME_PARSE() to turn the value back into the correct value.

[Databaser]

I’m confused… are we talking about concatenating a date + duration field here, or a date + time field (since your field says “convert TIME to text”)?

Tim_Sullivan:

I’m trying to make it look like: “12/29/2020 7:00 AM”

I was going to say: just use the “include a time field” option in your date field…

[Tim_Sullivan]

Sorry, it IS confusing.

I have a date field in one base, a duration field in another base, and in the third base, I’m trying to combine them. I’m using a duration field because I’m not aware of how to show only the time without a date.

[Databaser · Accepted solution]

OK. I think… :slightly_smiling_face:

If you have a paid version, you could use the “Eazyfields” app for a “time” field. Then you can easily use this formula to concatenate things:

DATETIME_FORMAT({date.},"DD-MM-YYYY")&" "&Time

• {date.} = your date field
• Time = the time field from Eazyfields app

Ofcourse you can change the settings to get your preferred datetime format.

Does this work for you?

[Tim_Sullivan]

That just might do it - I’ll play with it today at work. Thanks for the tip!

[Tim_Sullivan]

That did exactly what I wanted it to do, thanks again for the reply.