Legge inn Datoer uten skilleark

August 2  by Eliza

Ulike mennesker inn data på ulike måter. Når du skriver inn informasjon i en celle, forsøker Excel å finne ut hva slags informasjon du går inn. Hvis du angir et nummer som 08242008, antar Excel du går inn i en numerisk verdi, og behandler det deretter. Hva hvis nummeret du angir faktisk er en dato, uten separatorer? Kan Excel forstår hva du skriver inn?

Dessverre Excel kan ikke. Hvorfor? Fordi du har gitt det ingen indikasjon på at dette bør være en dato. (Excel taster på separatorer, ikke på tallverdier.) Hvis du eller dine data folk ikke kan endre sine innspill vaner, slik at separatorer er også lagt inn, så vil du trenge noen form for en midlertidig løsning for å konvertere lagt inn informasjon til en faktisk dato verdi .

Din første tanke kan være at du kan bruke en egendefinert format for å vise informasjonen. Vurdere følgende egendefinerte format:

## "/" ## "/" ####

Dette formatet vil vise nummeret 08152008 som 8/15/2008. Det eneste problemet er at det bare endrer visningen av nummeret-hvis du ønsker å bruke den dato som en ekte Excel dato, kan du ikke gjøre det fordi du ikke har konvertert verdien til noe som Excel gjenkjenner som en dato.

Dersom verdiene innspill var veldig konsekvent i sitt format, og hvis de var innspill som tekst i stedet for som numeriske verdier, så er det en enkel måte kan du konvertere dem til datoer. Ved veldig konsekvent, jeg mener at inngangs alltid brukt to sifre for måneden, to for dagen, og fire for året. I tillegg må de celler som inneholder de verdier være formatert som tekst. I dette tilfellet, kan du følge disse trinnene:

  1. Velg kolonnen av datoer.
  2. Pass på at det er ingenting i kolonnen bare til høyre for datoene.
  3. Velg Tekst til kolonner fra Data-menyen. (I Excel 2007, velger du Tekst til kolonner fra kategorien Data av båndet.) Excel viser konvertere tekst til kolonner Wizard. (Se figur 1)

    Legge inn Datoer uten skilleark

    Figur 1. konvertere tekst til kolonner Wizard.

  4. Velg fast bredde alternativet, klikk deretter på Neste.
  5. Klikk på Neste igjen.
  6. I kolonne Data Format området, velger du Date.
  7. Merk området i Destination boksen, deretter i regnearket klikker cellen rett til høyre for den første verdien du valgte i trinn 1.
  8. Klikk på Fullfør.

Hvis alt gikk bra, bør Excel har analysert tekstverdiene som datoer, og du kan slette den opprinnelige kolonnen. Hvis dette ikke fungerer, så betyr det at enten de opprinnelige verdier ble ikke formatert som tekst, eller åtte sifre ble ikke brukt til å skrive inn alle datoene.

En annen mulig løsning er å bruke en formel for å konvertere de angitte verdiene til faktiske datoer. Følgende er en slik formel:

= DATE (HØYRE (A1,4), VENSTRE (A1, IF (LEN (A1) = 8,2,1)), LEFT (HØYRE (A1,6), 2))

Denne formelen forutsetter at den oppgitte datoen (den uten separatorer) er i celle A1. Formelen vil arbeide med enten syv eller åtte-sifrede datoer.

Hvis du foretrekker egendefinerte funksjoner, kan du opprette en i VBA som undersøker dataene blir vedtatt, konverterer den til en dato / tid-format, og returnerer deretter resultatet. Følgende funksjon er svært allsidig i denne forbindelse; det vil fungere med både amerikanske og europeiske datoformater:

Funksjon Datetime (dblDateTime Som Double, _
Valgfri bAmerican Som Boolean = True)
'Konverterer Dato og tid "nummer" uten
'Skilletegn i en excel serialdate (som
"Kan da være formatert med Excel
'dato / klokkeslett formater)

«Hvis valgfritt argument er TRUE (eller mangler),
'Funksjon foruts verdi er av formen:
"[M] mddyyyy.hhmm (ledende" 0 "ikke nødvendig)

«Hvis valgfritt argument er FALSE, funksjon
'Antar verdien er av formen:
"[D] dmmyyyy.hhmm (ledende" 0 "ikke nødvendig)

Dim iYear As Integer
Dim iMonth As Integer
Dim iday As Integer
Dim iHour As Integer
Dim Imin As Integer

iYear = Int ((dblDateTime / 10000 - _
Int (dblDateTime / 10000)) * 10 000)
iday = Int ((dblDateTime / 1000000 - _
Int (dblDateTime / 1000000)) * 100)
iMonth = Int ((dblDateTime / 1000000))
iHour = Int ((dblDateTime - Int (dblDateTime)) * 100)
Imin = Int ((dblDateTime * 100 - _
Int (dblDateTime * 100)) * 100 + 0,5)

Hvis bAmerican Deretter
Datetime = Dateserial (iYear, iMonth, iday)
Else
Datetime = Dateserial (iYear, iday, iMonth)
End If

Datetime = Datetime + (iHour + Imin / 60) / 24
End Function

Dette makrofunksjonen antar at dataene som sendes til det er en numerisk verdi, som normalt ville skje når inngående datoer uten separatorer. (Se tilbake til logikk på dette i begynnelsen av spissen).

Som du kan fortelle, er det en rekke av midlertidige løsninger, men ingen av dem er så enkelt som bare å legge inn separatorer når du taster inn datoene. Hvis trening selv eller dine data input folk til å gjøre dette er vanskelig, kan du vurdere å sette opp noen data valideringsregler for inngangs celler. Disse reglene kan sjekke for å være sikker på at du kommer inn informasjon ved hjelp av en bestemt format (slik som en date med separatorer), og stoppe deg hvis du ikke er. (Hvordan du oppretter datavalideringsregler har blitt dekket i andre saker av ExcelTips.)

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (11400) gjelder for Microsoft Excel 2007 og 2010. Du kan finne en versjon av dette tipset for den eldre menygrensesnittet av Excel her: Entering Datoer uten skilleark.