 Pontiac FreakLooking for 1971-1976 GM Station WagonsPremium
join:2000-07-18
Mesa, AZ | [Excel] Simple Excel Question Hopefully easy and I just havent figured out how to do it yet.  I have a spread sheet that I have to enter 2 columns into and I want to have one column auto populate based on the first column value.
Column 1------- Column 2
10:48 ---------- 104828
I enter the values in Column 2, I need to remove the last 2 digits of column 2 value and format to time in column 1 automatically. Whats the easiest way to do this?
--
Hit Hard Or Stay Home!
More Hits Here |
|
 Wily_OnePremium
join:2002-11-24
San Jose, CA | You will need to become friends with Text formulae.
Basically you need to have an operation that does what you just stated verbally: strip off last two characters of Column 2 into Column 1. Then you just format Column 1 as Time format.
Hint: =LEFT(B1,4) |
|
|
|
Pontiac FreakLooking for 1971-1976 GM Station WagonsPremium
join:2000-07-18
Mesa, AZ | Thanks! That does work for pulling the data in, but how to format the cell now to display it as 10:48 from the original 1048? |
|
Wily_OnePremium
join:2002-11-24
San Jose, CA | Well come on now, think about what has to be done. Look at the various text formulae and play around until you get what you want. That's the best way to learn! |
|
 KilroyPremium,MVM
join:2002-11-21
Ann Arbor, MI | reply to Pontiac Freak
=LEFT(B1,2) & ":" & MID(B1,3,2)
Pressed F1 and typed in "text formula format" selected the article Combine text and numbers the Combine text and numbers from different cells into the same cell by using a formula.
--
When will the people realize that with DRM they aren't purchasing anything? |
|
 PhoenixDown-- Wants FIOSPremium
join:2003-06-08
Fresh Meadows, NY
kudos:1 | reply to Pontiac Freak
=TIME(LEFT(B3,2),MID(B3,3,2),0)
=Time(Hour, Min, Sec)
=Left() to get the hour
=Mid() to get the minutes from the middle
This assumes the Col 2 is in 24 hour time format.
--
1/22/2012 Delegate Count
Newt 25 | Romney 14 | Ron Paul 10 | Santorum 8
|
|
Pontiac FreakLooking for 1971-1976 GM Station WagonsPremium
join:2000-07-18
Mesa, AZ | reply to Pontiac Freak
that did it, thanks!! |
|
