Duration Calculation in a Formula Column based on Start Time and End Time

FAQs
,
#1

Dear Community and Experts,

I have this following subitems to track for the time dedicated to a program:

image
image920×151 13.2 KB

Would you please help me to solve the following issue:

  1. How to do the formula of Column Duration based on the Column {End Time} and {Start Time}? (The duration shall be in minute. and the time format is American format)
  2. How to make sure that {End Time} Shall never be smaller than {Start Time} please?
  3. How to do to have a summary under Duration so that we know the total Minutes spent for all the programs?

The #2 and #3 are just bonus, but what matters the most is the #1 question if you may help me resolve it please.

Thank you in advance!
TR

#2

Thank God, I found the solution after digging deeper,

The formula should be:

CONCATENATE((LEFT(HOURS_DIFF({End Time},{Start Time}),2)*60)+RIGHT(HOURS_DIFF({End Time},{Start Time}),2)," mn")

Unfortunately, that produces text and is not calculable, so I renounced to my idea of having a subtotal.

But I still don’t know how to warn the user if the {Start Time} is greater than {End Time}

#3

@rakotonirinaT

This formula might serve you better:
IF({End Time} = "", "", IF({End Time} < {Start Time}, "🟥ERROR🟥", TIMEVALUE("1/1/1 " & {End Time}) - TIMEVALUE("1/1/1 " & {Start Time})))

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: A Status Column Tip - When is “Done” really DONE?
Contact me directly here: Contact – The Monday Man