SOLVED

mm:ss problem

%3CLINGO-SUB%20id%3D%22lingo-sub-3537173%22%20slang%3D%22de-DE%22%3Emm%3Ass%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3537173%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%2C%20want%20to%20ask%20why%20theres%20a%20problem%20when%20i%20choose%20format%20mm%3Ass%20there%20always%20displaying%20a%20date%20and%20hh%3Amm%3Ass%3F%3F%3F%20i%20want%20ecel%20that%20it%20accepts%2030%3A00%20(min%2C%20sec.)%20if%20i%20write%20in%20the%20cell.%20But%20it%20doesn't.%20Is%20it%20a%20error%20from%20programming%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(9).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F382646iFBBBC4A4FE3B2C9A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(9).png%22%20alt%3D%22Screenshot%20(9).png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(10).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F382647i1A854798EE363C13%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(10).png%22%20alt%3D%22Screenshot%20(10).png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(11).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F382648i3BE5F79CD51B81C6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(11).png%22%20alt%3D%22Screenshot%20(11).png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Screenshot%20(12).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F382649i42D5BB9C9BAA2538%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(12).png%22%20alt%3D%22Screenshot%20(12).png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3537173%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3537215%22%20slang%3D%22de-DE%22%3ERe%3A%20mm%3Ass%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3537215%22%20slang%3D%22de-DE%22%3EMaybe%20go%20back%20to%20german%3A%20I've%20selected%20a%20cell%2C%20click%20Format%2C%20and%20then%20click%20Custom.%20Then%20I%20select%20mm%3Ass!%20If%20I%20now%20enter%2030%3A00%20in%20the%20cell%2C%20and%20press%20enter%20or%20leave%20the%20selection%20of%20the%20cell%20Excel%20shows%20me%20in%20the%20cell%2000%3A00.%20If%20I%20then%20click%20on%20it%20again%2C%20the%20command%20display%20says%3A%2001.01.1900%2006%3A00%3A00.%20That%20makes%20no%20sense%20at%20all%3F!%20I%20would%20understand%20if%20I%20entered%20more%20than%2060%3A00%20minutes.%20But%20why%20is%20there%20a%20problem%20here%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3537254%22%20slang%3D%22de-DE%22%3ERe%3A%20mm%3Ass%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3537254%22%20slang%3D%22de-DE%22%3E%3CP%3ETimes%20are%20always%20stored%20and%20calculated%20in%20Excel%20as%20fractions%20of%20days.%3CBR%20%2F%3EAn%20input%20without%20hours%20is%20therefore%20not%20possible.%3CBR%20%2F%3ESince%20these%20are%20minutes%2C%20you%20must%20always%20enter%20the%20input%20with%20hours.%3CBR%20%2F%3EExample.%2000%3A30%20is%20for%2030%20minutes%2C%20or%2000%3A30%3A15%20is%20for%2030%20minutes%20and%2015%20seconds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDon't%20know%20if%20it's%20possible%20with%20your%20Excel%20version%20and%20storage%20medium%2C%20but%20you%20could%20also%20use%20VBA%20code.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20If%20Not%20Intersect(Target%2C%20Range(%22A1%3AA10%22))%20Is%20Nothing%20Then%0A%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20Target.Value%20%3D%20Target.Value%20%2F%2060%0A%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3537300%22%20slang%3D%22de-DE%22%3ERe%3A%20mm%3Ass%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3537300%22%20slang%3D%22de-DE%22%3EOk%2C%20thank%20you%20very%20much.%20I%20inserted%20it%20now%20with%2000%20hours%20and%20it%20worked.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3537513%22%20slang%3D%22de-DE%22%3ERe%3A%20mm%3Ass%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3537513%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1432483%22%20target%3D%22_blank%22%3E%40rissestefanwebde%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20glad%20that%20I%20could%20help%20you.%3C%2FP%3E%3CP%3EWish%20you%20further%20success%20with%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, want to ask why theres a problem when i choose format mm:ss there always displaying a date and hh:mm:ss??? i want ecel that it accepts 30:00 (min, sec.) if i write in the cell. But it doesnt. Is it a error from programming?

Screenshot (9).pngScreenshot (10).pngScreenshot (11).pngScreenshot (12).png

4 Replies
Vielleicht nochmal auf deutsch: Ich habe eine Zelle ausgewählt, klicke auf Formatieren und dann auf Benutzerdefiniert. Anschließend wähle ich mm:ss aus! Wenn ich nun 30:00 in die Zelle eingebe, und enter drücke oder die Auswahl der Zelle verlasse zeigt mir Excel in der Zelle 00:00 an. Wenn ich dann nochmal draufklicke steht in der Befehlsanzeige: 01.01.1900 06:00:00. Das macht überhaupt kein Sinn?! Ich würde es verstehen, wenn ich mehr als 60:00 Minuten eingebe. Aber warum ist hier ein Problem?
best response confirmed by rissestefanwebde (New Contributor)
Solution

Zeitangaben werden in Excel immer als Bruchteile von Tagen gespeichert und berechnet.
Eine Eingabe ohne Stunden ist daher nicht möglich.
Da es sich um Minuten handelt müssen Sie die Eingabe immer mit Stunden eingeben.
Beisp. 00:30 ist für 30 Minuten, oder 00:30:15 ist für 30 Minuten und 15 Sekunden.

 

Weiß zwar nicht ob bei Ihrer Excel Version und Speichermedium es möglich ist, doch Sie könnten auch VBA Code nutzen.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
      Application.EnableEvents = False
      Target.Value = Target.Value / 60
      Application.EnableEvents = True
   End If
End Sub
Ok, vielen dank. Ich habe es jetzt mit 00 Stunden eingefügt und es hat geklappt.

@rissestefanwebde 

Es freut mich, dass ich Ihnen helfen dürfte.

Wünsche Ihnen des weiteren viel Erfolg mit Excel.

 

NikolinoDE