Time in a cell

[wayneg]

i asked earlier about how to change text to time in a cell, and the solution was given to me (thank you Debraj Roy), but after marking as solved i copied the cell down, but a new problem came. if the cell already had a time in it, it left the new cell blank. so i altered the formula to read

=IF(P6="","",IF(CELL("format",P6)="d9",P6,TIMEVALUE(P6))) 

which i think should have worked and q6 did show a time value, but my time calculations failed. did i do something wrong?

[:) Sixthsense :)]

Extract of microsoft excel help about how to use Timevalue

TIMEVALUE(time_text)The TIMEVALUE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Time_text Required. A text string that represents a time in any one of the Microsoft Excel time formats; for example, "6:45 PM" and "18:45" text strings within

Formula Description (Result)
=TIMEVALUE("2:24 AM") Decimal part of a day, for the time (0.1)
=TIMEVALUE("22-Aug-2008 6:35 AM") Decimal part of a day, for the time (0.274305556)

[wayneg]

im sorry sixthsense, what are you trying to tell me.... basically i copy something into p6 eg 20:30 i want q6 to look to see if it is in time format and if it is then copy cell, if it isnt then convert it (using timvalue)

[:) Sixthsense :)]

Please clarify what output you are looking from Timvalue function?

[wayneg]

i am pasting in text (10:30) and want it converting to time so i can do calculations with it.. the timevalue function works perfectly (as i have stated) but i manually have to look at original values to make sure they dont go over the midnight period. if it does i change the value (01:30 to 25:30) which automatically makes it a time format. this is where timefunction failed. so i used an if function to query the original cell so it looked to see if it was already in time format and if not do the convertion that timevalue is supposed to do. on its own timevalue worked, but with the if function first it didnt do as expected.

[:) Sixthsense :)]

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[wayneg]

i have attached a the workbook. i know i need my formulas tidying up on the sheets but at the moment i am still learning how to approach them (ie using min and max statements or something)

[wayneg]

sixthsense. on closer inspection to my formula, i think it is correct... i re-inputted the data and it worked... i dont know why it didnt work for the data that was there.

[:) Sixthsense :)]

Please dont kill your brain by applying nested ifs...

Your's can be much more simplified by using the vlookup/lookup or sumproduct in one line formula....

Just attach a workbook by covering the 5 examples with expected output so that we will suggest a simple method for you...

[wayneg]

im not really sure on vlookups, etc... but i have been trying to get my head round them

i'm assuming you are referring to all my nested if's in column F on the theatre times page. i will endeavour to find out how to use it... but that will be for a different thread on a different day so im not flooding the forum