Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# Dates and formulas question

Solved
477 7
cancel
Showing results for
Did you mean:
7 - App Architect

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.

1 Solution

Accepted Solutions
11 - Venus

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?

7 Replies 7
11 - Venus

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

7 - App Architect

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.

11 - Venus

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”)?

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

7 - App Architect

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.

11 - Venus

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?

7 - App Architect

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

7 - App Architect

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