Subtracting minutes from a set value

[Kellbell]

Hello and thanks in advance from you time helping with this problem.

I am using Excel 2007 on a PC and wish to do the following, have excel return a value for the time my son plays as a hockey goalie based on the time the clock show when he is switched with his goalie partner. For example commonly, he plays the first period and 10 minutes of the second period, which should produce a product of 30 minutes. The problem is at his level, Atom, they play a 15 minute 1st and 15 minute second, with a 20 minute third. I have spent the past 90 minutes trying and reading posts, and trying, and reading etc. This is what I started with.

A B
Period 1
Time pulled 12:05:00 AM
Time played =IF(OR(B1=1,B1=2),12:15:00 AM-B2,12:20:00 AM-B2)

I get an error in the formula that I can't solve. It doesn't like the time format. I have tried many different versions of the formatting with no success. I am a novice user of excel and enjoy the challenge of solving these problems on my own, but I can't seem to figure this one out.

I'm being careful to enter the 'time pulled' data as 0:05:00 but i guess I'm missing something. Further I did see an example in these forums regarding using a VBE, which looks interesting, in order to not need to enter the colon every time. But I would first like to get this formula working and then work on tweaking it.

Any help is most appericated.

Thanks,
Kelly

 

[Jonmo1]

Welcome to the board...

Perhaps

=IF(OR(B1=1,B1=2),"12:15:00 AM"-B2,"12:20:00"-B2)

 

[Kellbell]

Jonmo1,
Thanks very much, that was all it was; the "" needed to contain the time value. You rock thanks!!

As a 'next step'. do you have an easy way to be able to enter the following string

527

or

0527

and have it be read by excel as 00:05:27

If there is no easy way perhaps there is a more complicated way that I might try.

Thanks again for your very quick reply...
Kelly

 

[Jonmo1]

Not directly in the cell as you type it I think you would need VBA code to do that. But you can put a formula in an adjescent column to convert

=TEXT(A1,"00\:00\:00")+0

 

[Kellbell]

Thank again, I was able to combine your two suggestion and ended up with this:

=IF(OR(B6=1,B6=2),"12:15:00 AM"-(TEXT(B7,"00\:00\:00")+0),"12:20:00"-(TEXT(B7,"00\:00\:00")+0))

I then just enter the 1,2,3 or 4 digits representing the time showing on clock and the time played shows perfectly. Of course the 'time pulled' ends up as a serial number that is out of wack but I don't really need it anyway.

Thanks for your expert help.
Kelly

 

[Jonmo1]

is your 4 digit entered time Hours and Minutes, or Minutes and Seconds ??

0527 - is that 5 hours 27 minutes, or 5 minutes 27 seconds ?

if it's Hours and minutes, then formula is
=TEXT(A1,"00\:00")+0

if it's Minutes and Seconds, then formula is
=TEXT(A1,"00\:00\:00")+0

 

[Kellbell]

It's minutes and seconds. But the formula returns the correct value??? [Edit, sorry I see that the correct string was provided by you the first time and that is the string I used and that is why it works.... duh]

One other question, I opened the VBA window on this workbook by right clicking the worksheet tab and choosing view code. I did NOT enter anything in that window (for this workbook) and closed the editor. Now I get an error warning me about personel information that will not be picked up by some file checker. Is there any way to stop that? Apparently I have added something to the file by opening the VBA and I just want to remove that.
Thanks,
Kelly

 

[Jonmo1]

Did you do anything in VBA? Did you click Insert - Module, or UserForm ? Even if you didn't enter any code in either of those windows, just the existance of a module will trigger the Macro Security message..

 

[Kellbell]

Jonmo1
Don't waste your time, shutting down excel and opening the file has taken care of warning screen.
Thanks for all your help, your are a wealth of info...
Kelly