# Help with Date Calculation Formula

I need to be able to submit a date/time as 1 minute intervals. I have created four fields.

• Date (Date only field)
• Hour (choice drop down 12 AM - 11 PM)
• Minute (choice drop down 00 - 59)
• DateTime (calculated field)

I would like to combine the first three fields into a single date/time field for the DateTime field.

Example: 4/4/2019 9:27 AM

Is there a formula I could use merge Date, Hour and Minute as one?

Thanks.

Here's one solution, using numbers:

``````=Date
+ IF(Hour="12 AM",0,
IF(Hour="12 PM",12,
(LEFT(Hour,2)+IF(RIGHT(Hour,2)="PM",12,0))) / 24 )
+ (Minute/60/24)
``````

And another just processing text:

``````=TEXT(Date,"mm/dd/yyyy") & " " & LEFT(Hour,2) & ":" & Minute & " " & RIGHT(Hour,2)
``````

The second is probably the best choice, unless you have other date math to add to the calculation. (Timezone offset, etc)

Both assume your hours and times are zero prefixed and have a space in the middle: "01 AM", "10 PM", etc.

It's interesting how the "number" version outputs without leading zeros, even though it is generating a date serial number. Must be something about how SharePoint formats calculated dates. You can force either output by wrapping your formula in a TEXT function.

``````=TEXT( yourFunctionThatReturnsADateSerialNumber, "mm/dd/yyyy hh:mm AM/PM") 