 # Convert time to a usable format in a formula

• Hello all

Im trying to convert a time formatted as 00:00:00

Rather than typing in 3 different columns for hrs, min, secs - I would like to be able to enter a time, for example in cell C11 enter 12:25:15 and have this number converted in cell D11 (formatted as number) to a total no of seconds, this being 44715 for the example to the left.

Currently, I am using =C11*86400 which gives me the correct answer in D11 but the calculation cannot be used in another formula without error.
I tried converting using n() and Value() but this did not work?

Any thoughts

Thank you

• Re: Convert time to a usable format in a formula

What is the formula you are trying to apply the result to?
Why can't you use the time in D11 in that formula instead?

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Convert time to a usable format in a formula

HI

It is a simple division =E12/D11 but there may be more referencing this cell "D11"
I cant use the time in the cell from D11 as this is not in seconds?
Unless, there a way to referrence the value in D11 and convert into seconds within the formula so that the answer is in total seconds and this value be recognised as a "number" and used in other calculations.

Thank you

• Re: Convert time to a usable format in a formula

unless D11 is 0 or E12 is not numeric, you shouldn't get an error,

Can you post a small sample workbook showing the issue?

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Convert time to a usable format in a formula

HI

I made a msitake the format of the cells which have the time entered, also have the date entered.
They are formatted as dd/mm/yy hh:mm:ss

I assume this is why calculations dont work?
However, I do need the date as well as the time to calculate durations that run over 1 day

Thank you

• Re: Convert time to a usable format in a formula

Hello,

With your input cell in C11 ... in order to get your time expressed in seconds,

you could use following formula :

Code
``=C11-INT(C11)``

and format the result : Custom [s]

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner • Re: Convert time to a usable format in a formula

Hi Carim

Just to reitterate what I am trying to accomplish...
I hope this is clear...

Cell A1: An input cell for a Start date and Time formatted as dd/mm/yy hh:mm:ss
Cell A2:
A conversion formula for Cell A1 into seconds [ss].
Cell A3: An input cell for a Stop date and Time
formatted as dd/mm/yy hh:mm:ss
Cell A4:
A conversion formula for Cell A1 into seconds [ss]. "If the date > A1 as well as times, then the todal days and time duration needs to be calculated"
Cell A5: A calculation to find the difference in time between A2 & A4 in Seconds formatted as [ss]
Cell A6: An input cell for Distance in Yards
Cell A7: A conversion formula into Sixtieths. "Yards*60 for greater accuracy"
Cell A8: A calculation for Velocity in MPH. =((A7/A5)/1760)*60 (
Velocity = Distance/Time)
Cell A9:
A calculation for Velocity in YPM "Yards per minute" =(A7/A5)/1760 (Velocity = Distance/Time)

Using an example of...
A1: 04/05/17 12:00:00
A2: = ????? "Formula required here but 43200 is the answer"
A3:
04/05/17 12:30:00
A4: = ????? "Formula required here but 45000 is the answer"
A5: = A2/A4 "1800 is the answer"
A6: = 1760 "Yards"
A7: = A6*60 "Yards*60 Greater accuracy"
A8:
= ((A7/A5)/1760)*60 "2 mph is the answer"
A9: = (A7/A5)/1760 "58.67 YPM ais the answer"

Thank you

• Re: Convert time to a usable format in a formula

Hello again,

Your explanation is very clear ...:wink:

But what happens in cell A2 ... if you apply the suggested formula ...and format the result: Format Cells > Number > Custom Type: [s]

Code
``=A1-INT(A1)``

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner • Re: Convert time to a usable format in a formula

Attached is a sample workbook ... using the data from your example ...

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner • Re: Convert time to a usable format in a formula

Hi Carim

Everything was fine up to the point of calculating the YPM and MPH

I need to get my head around INT and MOD somewhat

How do I attah sample workbooks? I do not see an option to attach and upload !

Thank you

• Re: Convert time to a usable format in a formula

Hi Carim

Just noticed that the date and time calculations in [s] do not change if the date increases from the start date and time to the stop date and time?

Any thoughts?

Thank you

• Re: Convert time to a usable format in a formula

Hi,

In order to attach a workbook :

When you reply ... use the option (bottom right) Go Advanced ... and use the symbol paperclip ... Attachments (to the right of the Smiley..)

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner • Re: Convert time to a usable format in a formula

Pefect thank you

Did you see my last post re: date and time calculations not changing?

• Re: Convert time to a usable format in a formula

Hello again,

When changing cells A1 and A3 ...all formulas do adjust themselves ...

So, my assumption is that, you are also facing instances where the Stop Date is higher than the Start Date ...

To account for these cases, attached is the Version 2 ... for your review ...

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner • Re: Convert time to a usable format in a formula

Hi Carim

Sorry, I should have said that A3 is always the same date or greater

Works great thanks again :cheers:

• Re: Convert time to a usable format in a formula

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner 