1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

excel functions

Discussion in 'Business Applications' started by Lizhusain, Oct 14, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    Hi:

    I have several columns that look like this:

    10/28/2002 07:55_AM
    10/28/2002 02:50_PM

    (both) and

    10/28/2002 07:55_AM


    now I need a function that can delete the dates only and leave the time.

    Is this possible?
     
  2. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    There may well be a better solution than this one, but I know it works in Excel. If you have mm/dd/yy hh:nn in cell A1, use this in the cell where you want the time only:
    Code:
    =A1-ROUNDDOWN(A1;0)
    You will have to format the cells to show time only, or you will see 1/1/1900 for the date. Of course, you could just format a date column to show time only, too. But perhaps you need the actual time value only; this function will get it for you.

    Hopefully someone else will have something sexier than this.
     
  3. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    Hi:

    I'm sorry, you'll have to take into consideration that I know nothing about excel, can you please explain how to do this?

    thanks.
     
  4. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    Hi:

    Yes, I did that , but it won't work.
    What I should have explained is that the cells are copied directly from the internet, converted into excel, eg. it is flight information. But I don't need the date the flight is going/coming and time,all I want is the time.

    This is what I took:

    10/22/2002 06:55_AM

    All I want is the time, not date in my cells.
    Also, I have merged using copy and paste technique, and alt+enter to enter the next line and input another result in the same cell (that is how I want it). So one cell might look like the one above and the following might look like this:

    10/28/2002 07:00_AM
    10/28/2002 08:50_AM

    both of them in the same cell.

    What I want now is to convert those cells to show the time only. Be it the one with one line or 2, without having to delete it one by one.


    Isn't there some function that can delete the first 10 bytes in a cell and then from the 20th to the 30th or something? Has to be a way.
     
  5. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Why would you be copying 2 date/time values into a single cell? You'll never be able to get a formula to make that work, because there's no reason to have two dates in a single cell.

    Otherwise, I still can't tell from what you're asking if it's purely a formatting question (i.e. you don't want to "see" those date values but it doesn't matter if they're there), or it's a question of the actual value (i.e. if it's 10/10/02 15:00 instead of 15:00 that will screw up other dependent values).

    If it's just formatting: select the column in question; choose Format | Cells from the menus up top; select Time as your category; the dates will disappear.

    Otherwise, you need a second column; say the first column is A, and the first cell with a date is A1; in B1 you copy the formula I gave you:
    Code:
    =A1-ROUNDDOWN(A1,0)
    If you don't understand the concept of a formula, you should search for formula in Excel's online help (F1 key), as that's beyond the scope of something I can explain to you in a post, and Excel will do a better job anyway.

    You then copy this formula from cell B1, and paste it into all the other cells for which their is a match in A. In other words, if you have values in cells A1 - A30, you copy cell B1, and paste it into B2-B30; it will adjust the formula accordingly.

    However this formula will not work if you have multiple date values in a single cell. You need to understand how Excel works before you can try to make it do what you want it to do (however convinced you may be that there "must be a way"); one cell, one value. That's the point of using a spreadsheet in the first place, to be able to calculate values across multiple cells. It's not a value if there's two of them together in one cell.
     
  6. tjsudo

    tjsudo

    Joined:
    Jul 27, 2000
    Messages:
    524
    Hi,

    Say you have your data 0/22/2002 06:55_AM in cell A1.
    Type =Right(A1,8) in cell B1.

    I think you will get what you want. If you don't want _AM .
    Type =Left(B1,5) in cell C1.

    Will this work for you?

    TJ
     
  7. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    HI
    I think tjsud is on to something, that is exactly it, what happens is when i copied the time from the net, they are fixed.

    It worked for the ones with one row. however, for the cells with 2 rows such as:

    10/28/2002 07:00_AM
    10/28/2002 08:50_AM

    I can use the 8 from right, and some thing else to caption the 7:00 am.

    The problem is I won't be able to maintain this in order, ie. in the other line.
     
  8. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    I think this might be required some Visual basic functions.

    extracting from the contents.
     
  9. tjsudo

    tjsudo

    Joined:
    Jul 27, 2000
    Messages:
    524
    Hi,

    I can't recreate your situation.(Two data in one raw)
    I think there's a way to break those data into two raw.

    Point me to the site where I can copy and paste.

    TJ
     
  10. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    ok, here's the situation.

    it is flight information. I'm doing my own personal flight information sheet. e.g from NY to Ohio, might have connecting flights to get there or straight flights. NOw I have to put the connecting flights in the same row, same cell for time, e.g ie.

    Since, I'm sorting using autofilter to see the flights on a specific day, carrier, fligth number, departure city, departing time.

    I did a search for all flights going out mondays and coming in mondays, that way when i click on sort for monday (auto filter function) all related information for mondays will come up......ie , carier, time, departure/arrival bet. those two states. get it so far?

    Now, the departure time and arrival time has the dates i used to search on the net. I used AA site, since they can search for all carriers.

    If i use a different row / cell for connecting flights, they will not go together., if u want i can send u a copy of what the worksheet looks like.

    Thanks.

    hope this makes sense.
     
  11. tjsudo

    tjsudo

    Joined:
    Jul 27, 2000
    Messages:
    524
    I know exactly what you mean.

    I just can't create the situation.

    Take me to the site, give me a link.

    TJ
     
  12. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
  13. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    ah ha!

    here is the function that i created with your help:

    =MID(F41,12,9)&RIGHT(F41,8)

    that extracted from:

    10/28/2002 07:00_AM
    10/28/2002 08:50_AM

    with this result:

    07:00 AM
    08:50 AM

    I knew there had to be a way.:D
     
  14. tjsudo

    tjsudo

    Joined:
    Jul 27, 2000
    Messages:
    524
  15. Lizhusain

    Lizhusain Thread Starter

    Joined:
    Sep 13, 2002
    Messages:
    190
    hold your horses, well those little things jumping then.

    not over yet.

    now i need to combine and add an "IF" function to that. I have alternate cells with one row and two rows. how do i say if the cell contain > 19 characters to use the specific function and if the cell contains <=19 characters to use the first function?

    that should be a brain bust open for yo! :D :D


    and how did u get those little coloured fella that that jumps? :cool:
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/99655

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice