copy time formatted cell exactly to another cell but as text format.

[dazza67]

Hi All, I have tried doing this with formulas but cannot seem to get it to work.

I have a load of swimming times some as mm:ss.00 some as just ss.00 if under 1 minute, none of them go into over 1 hour.

so in a column in no particular order, i have some times like 01:15.90 or 05:25.79 (mm:ss.00) in others I would only have 32.40 or 50.98 (ss.00)

I need to copy the time formatted cell to the cell to the right as text formatted, so I can then use a mail merge, the mail merge I am using does not like the time format and changes it to a time (am/pm)

I am happy to do this with a formula if someone knows how but I have tried and unless I am missing something I cannot get it to work.

Hope I have covered everything.

Darren

[FDibbins]

others I would only have 32.40 or 50.98 (ss.00)

Is that really in Time format, or just a regular number? What do you see in the formula bar?

[dazza67]

They are definitely time formatted to mm:ss.00 the time are downloaded from another programme and if it's below 60 it does no show the minutes before the : so will only show 50.98 eg.

One thing I forgot earlier was, some of the cells may say dq or dnf and this would need to stay as the are.

Cheers

Darren

[MrShorty]

What kinds of formulas have you tried? It might help us see where you are going wrong, and avoid suggesting something you have already tried.

It seems to me that a simple =TEXT() function would work for this. Something like =TEXT(A1,"[mm]:ss.00"). Is that one you have already tried?
https://support.office.com/en-us/art...8-93d29371225c

[dazza67]

Hi All

Thanks for the replies, I have just had a closer look, for times over 60 seconds, it is formatted as mm:ss.00, for times under 60 second it it just a "general" formatting.

I tried the formula suggested, this is why I looked at it further as I thought this should work and I tried lots of variations of this and could not get it to work.

So now I need to be able to get the time formatted cells into the column to the right and also the non formatted cells to the right, all of them in text format.

I have attached a file to show example.

Darren

[MrShorty]

Going back to Ford's first question (post #2) -- some of the values are real time serial numbers and some of them are regular decimal numbers (decimal seconds). The mix of entry formats is going to make programming the solution more difficult, so I would first suggest that you decide what format you want to enter the data. Some of this probably depends on what your mail merge really wants.

If I assume that the text in elapsed minutes format ("[mm]:ss.00") is the preferred input for your mail merge, I would recommend that you enter all times as time serial numbers. This means including the minutes, even when you enter times less than a minute 50.4 seconds would need to be entered as 00:50.04.

Once all of the times are entered as real time serial numbers, then the =TEXT() function should work just fine.

[dazza67]

The issue is that I do not enter these times, they are coming from a download from another program, it puts them into this format, I have over 1000 swims meaning I would then need to change loads manually, if I was changing them manually, I would not need the formula or VBA.

Darren

[MrShorty]

I did not realize that you had no control over the input format.

In that case, the solution still seems to be the same -- convert all entries to time serial numbers, then use the TEXT() function. I would recommend some time with something like this to understand how Excel stores time as serial numbers (fractions of a day). http://www.cpearson.com/Excel/datetime.htm

From that, you should understand that real time serial numbers are values less than 1. Your decimal seconds numbers will be greater than 1 (I will assume at this point that there will never be a time less than 1 second). Steps:

1) An IF() function to test if the value is greater than 1. =IF(A1>=1,...)
2) If greater than or equal to 1, then this entry is a decimal seconds value and needs to be converted to fraction of a day. There are 86400 seconds in a day, so divide the value by 86400. =IF(A1>=1,A1/86400,...)
3) If not, then this is a regular time serial number, so don't change it. =IF(A1>=1,A1/86400,A1) That will convert all numbers to time serial numbers. Text strings will trigger an error.
4) Convert numbers to text in the desired [mm]:ss.00 format using the text function. =TEXT(IF(A1>=1,A1/86400,A1),"[mm]:ss.00")
5) Use an IFERROR() function to trap the text errors and pass the text string through. =IFERROR(TEXT(IF(A1>=1,A1/86400,A1),"[mm]:ss.00"),A1)

If you need help understanding the functions used: https://support.office.com/en-us/art...90033e188#bm19

Does that do what you need? Where do you get stuck? Is there more to your question?

[sanram]

You can try this :

Please Login or Register  to view this content.

[Pete_UK]

You could put this formula in D6:

=IF(OR(B6="DQ",B6="DNF"),B6,TEXT(B6/IF(B6<1,1,60*60*24),"[m]:ss.00"))

then copy down to the bottom of your list.

Hope this helps.

Pete

[dazza67]

Thanks again for the replies, I have tried both, Sanran, this does not work as it appears to be rounding up the times to the nearest second, also when it comes to DQ or DNF, it shows #value!.

MrShorty, this may seem like I am being really fussy, all works but it now shows the times below 60 seconds as 00:50.34, all I want to see is 50.34, this is being mail merged on to certificates and will not look right with the extra 00: other than that, it worked great, can it be modified to take this into account?

Darren

[Pete_UK]

Did you try my suggestion?

You can use this in D6 if you want to show sub-60 second times as fractions of seconds:

=IF(OR(B6="DQ",B6="DNF"),B6,TEXT(B6,IF(B6<1,"[m]:ss.00","0.00")))

Hope this helps.

Pete

[sanram]

You can try this :

Please Login or Register  to view this content.

[dazza67]

Hi guys, thanks for the replies, sorry, I went to bed after my last post, could not keep my eyes open.

Pete, your suggestion works great, thanks.

Sanram, your formula has the same issue as MrShorty, does most of the bit i need but on the cells less than 60 seconds, it shows 00:50.40 rather than 50.40.

I was going to mark this as solved as Petes formula does what I need but I can't see how to, as its not VBA should it be moved too?

Cheers guys

Darren

[Pete_UK]

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED. No need to move it.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[sanram]

You are welcome and thanks for the rep.

You need to change the format on my formula to get only seconds like this :

Please Login or Register  to view this content.

Also you can use this :

Please Login or Register  to view this content.