Stopping the Time Auto-Format!

[NateG]

Hi there, first post on the forums... any help would be very much appreciated, as I've really been struggling with this. I'm a very novice excel user (I'm a university student), and am having trouble with the autoformatting that is occuring when I paste data in the x:xx format

I am pasting time on ice statistics from the NHL site. Whenever I paste anything that is in the x:xx format however, it gets converted to a time of day. So when I paste 4:14, which represents four minutes and fourteen seconds, it comes up as 4:14:00 AM.

How can I stop this from occuring?

Also, as an added bonus, perhaps someone might be able to tell me the quickest way to get at my ultimate goal of converting the time entirely to seconds. When I past 4:14 in, I'd like to convert it into strictly seconds, so 4 minutes (240 seconds) plus 14 seconds, so 4:14=256. I haven't figured out the easiest way to do this yet because I can't get past the first step of removing the auto-formatting.

Again, any help would be greatly appreciated, this is driving me absolutely nuts. Thanks!

[Bryan Hessey]

Hi there, first post on the forums... any help would be very much appreciated, as I've really been struggling with this. I'm a very novice excel user (I'm a university student), and am having trouble with the autoformatting that is occuring when I paste data in the x:xx format

I am pasting time on ice statistics from the NHL site. Whenever I paste anything that is in the x:xx format however, it gets converted to a time of day. So when I paste 4:14, which represents four minutes and fourteen seconds, it comes up as 4:14:00 AM.

How can I stop this from occuring?

Also, as an added bonus, perhaps someone might be able to tell me the quickest way to get at my ultimate goal of converting the time entirely to seconds. When I past 4:14 in, I'd like to convert it into strictly seconds, so 4 minutes (240 seconds) plus 14 seconds, so 4:14=256. I haven't figured out the easiest way to do this yet because I can't get past the first step of removing the auto-formatting.

Again, any help would be greatly appreciated, this is driving me absolutely nuts. Thanks!

Hi,

to post 4 mins you need 0:4:14

to convert 4:14 to 0:4:14 divide by 60

to convert 0:4:14 to seconds multiply by 86400

to convert the incorrect 4:14 to seconds multiply by 1440 (note, 254 not 256)

hth
---

[Barak]

Assumption:
In cell A1: 4:14


Try this:
In cell B1: =A1/60
And the format of cell B1: [ss]

[NateG]

I am very grateful to both of you, both worked out. Thanks for helping out an excel newb!

[Bryan Hessey]

I am very grateful to both of you, both worked out. Thanks for helping out an excel newb!

good to see that it worked for you, and thanks for your reply.

---