1

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.

4

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")
=TEXT( yourFunctionThatReturnsADateSerialNumber, "m/d/yyyy h:mm AM/PM")

enter image description here

  • Thanks Mike! The second choice is working pretty good. But the DateTime field ends up like this: 04/01/2019 4 :09 PM. Note the space between the hour and the colon before the minute. Is this because I don't have my Hour formatted as two digits? I have them like this: 12 AM, 1 AM, 2 AM, etc... – gsr51 Apr 4 at 19:17
  • Wait, I wrapped it with your second example line 2 at it looks pretty good. =TEXT(TEXT(Date,"mm/dd/yyyy")&" "&LEFT(Hour,2)&":"&Minute&" "&RIGHT(Hour,2),"m/dd/yyyy h:mm AM/PM") – gsr51 Apr 4 at 19:22
  • Thanks for the help Mike. – gsr51 Apr 4 at 19:23
  • 1
    "Is this because I don't have my Hour formatted as two digits?". Yes. – Mike Smith - MCT - MVP Apr 4 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.