Decimal time to hours and seconds within a formula field?

Hi Guys,
Can anyone help me with the correct mathmatical formula without having to use any text or format information please?
I’ve worked out that as soon as I format the output, I cannot sum the column, however if it is numbers only then it will sum properly.

this is the mathematical equation.

4.27 hours


but I’m not sure how to make that with in the formula field?

Hi @johnners,

Thanks for your question.

It sounds like our expert formula team might be best equipped to assist you with this one! Would you be happy to forward your question (and any relevant screenshots, i.e. of the formula you’ve built in the platform, and associated columns) to our support team via They will be able to put you in contact with our formula team who will happily work with you to achieve your end goal.

Thanks in advance :smiling_face:


Hi @BiancaT

This was actually sent over to the support team, they came back 8 days ago and said they were forwarding on to the formula team but I’ve not had any answers yet, which is why I was hoping someone here could help.

Hi @johnners

Jumping in here for @BiancaT!
Could you please check your spam folder in your email inbox? Emails from the support team can sometimes end up here by accident.

If not, are you happy to share with us your email address or account URL here so we can take a closer look for you?



Hi all,

I’m still struggling to get anywhere with working out how to format this formula - does any one have any ideas, but only using numerical information at all?

I’ll give a bit more info - maybe I’m making it too complicated…

This is the board at the moment.
and you can see we have a start and stop time (Hour) column.

I have a formula column called duration which gives us the duration in hours and minutes using HOURS_DIFF({Stop Time},{Start Time})

however we cannot sum this column, so I then added time spent column which is a formula saying:
ROUND(SUM(ROUNDDOWN(SUBSTITUTE({Duration HH:MM},":","."),0),DIVIDE(SUBSTITUTE({Duration HH:MM},":",".")-ROUNDDOWN(SUBSTITUTE({Duration HH:MM},":","."),0),0.6)),2)

so that changes it to a decimal eg 0.03 instead of 0.02 minutes

I’m looking for a way to have a sum at the bottom but have it show the hours:minutes which will assist us to see a total for that group on the board… Is there a way to get a column to show the actual time taken (03:17) and be able to show a sum at the bottom?


What you are looking to do is completely reasonable. However, there is not currently a way to show separate hours and minutes in a column summary (except for a time tracking column). Actually, you might consider using a time tracking column if you have not already.

Jim - The Monday Man
:magic_wand: Column Magic :sparkles:- the magical columns toolbox app

@JCorrell Hi Jim,
Thanks for your input - We started out using time tracking columns but we use mostly sub items and we can’t pull out the required reports from the time tracking columns within the widgets etc, so to get around this we have had to bring in a “Timesheet” for each user, which using automations and, is filled with the start and end times of each job - that way we can then show the daily information to the project managers screen, whilst having time trackers for detailed logging.

Its a nightmare how many shortcomings there are with the sub items, so we are constantly having to find work arounds.

All we want is a way to sum the column with HH:mm (just to make it easier so the project manager can see 6h 25m rather than 6.42 lol

I was hoping that if we could keep the output numerical rather than with formatting then it would sum correctly.

Hi @johnners

If you would be willing to add a couple more columns and visualize the totals in two different columns, you could do this:
Hours: ROUNDDOWN({Time Spent},0)
Minutes: ROUNDDOWN(({Time Spent}-ROUNDDOWN({Time Spent},0))*60,0)
If you keep the hours and minutes in separate columns, this should help to achieve what you’re looking for!