help with time in excel

Topic

New Reply

Hi there in excel when you custom time ie hh.mm when I input it it doesn’t do what I type any ideas please

Reply | Quote

Replies

Mar27,

Ok your custom format has a period (.) between the hours & minutes. However, when you enter the value you must use the colon[noparse] (:)[/noparse] between the minutes and seconds for Excel to recognize that you are entering a time. If you do this the time will display as 10.45 even though you entered it as 10:45. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Mar27,

Ok your custom format has a period (.) between the hours & minutes. However, when you enter the value you must use the colon[noparse] (:)[/noparse] between the minutes and seconds for Excel to recognize that you are entering a time. If you do this the time will display as 10.45 even though you entered it as 10:45. HTH :cheers:

I have done that which has worked on most of my sheet but when I input ie 57:00 I am getting this instead 02/01/1900 09:00:00

Reply | Quote

I have done that which has worked on most of my sheet but when I input ie 57:00 I am getting this instead 02/01/1900 09:00:00

This is correct as you now have 57 hours from 1 Jan 1900 at midnight. hh:mm (however formatted) is clock time not a count of hours and minutes.

The question is – what are you using the data for? If just displaying an elapsed time then formatting the cell as text will do the trick. It becomes a bit more complicated if you want to use the numbers in a calculation.

Eliminate spare time: start programming PowerShell

Reply | Quote

Mar27,

Ok, I’m guessing you’re using the dd/mm/yyyy date format (European I’m guessing since you didn’t post your location).
57 Hours = 2 Days 9 Hours thus -> Counting from 1/1/1900 + 2 Days + 9 Hours = 2 Jan 1900 @ 09:00. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

All I am doing is using as a colmum as time it takes to sleep just for input only no date or formula in there but on one row is does what I say

Reply | Quote

Mar27,

I’m a bit at a loss to understand your worksheet.

Row 26 shows 20 hours to fall asleep but only 7:19 in bed?

Could you please explain the logic between the columns as it will surely help with arriving at a solution.

BTW: You can use this formula to calculate time in bed. [noparse]=($A3+1+$C3)-($A3+$B3)[/noparse]
Place in cell G3 then fill down.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi there no formula all that I am doing is using excel to recorded date but it when it imput the time to bed it comes up with what I say just need advice how to stop it thanks

Reply | Quote

You are obviously entering a time of some sort different than what the cell is formatted for. Could you give an example of what you input and the result that shows for that input?

B.

Reply | Quote

all that I am input in line d27 there was a few lines on other sheet the same on this one input time as I set it as 57:00 but then it goes too 02/01/1900 09:00:00 retiredgeek said before but I don’t no how to change it thanks

Reply | Quote

Try changing the time format to: [HH].MM

cheers, Paul

Reply | Quote

paul it set as hh.mm but still get the 02/01/1900 09:00:00 when I put any thing like 57:00

Reply | Quote

Hi Mar

I suspect what you really want is just an easy way to enter time values.

In my attached version you can enter 1, 2 , 3 or 4 digits
if you enter a single digit, it assumes it is hours. e.g 8 =>08:00
if you enter 2 digits, it assumes minutes e.g 57 => 00:57
if you enter a number with a decimal: left of decimal is treated as hours as follows:
20.3 =>20:30; 21.17 =>21:17; 8.25 =>08:25; 8.4 => 08.40
If you enter 3 digits, it is assumed to be hmm e.g. 625 =>06:25; 123 =>01:23; 755 =>07:55
if you enter 4 digits, it is assumed to be hhmm
..have a try, and see if this is what you want

zeddy

Reply | Quote

You must use the square brackets.

cheers, Paul

Reply | Quote

You must use the square brackets.

cheers, Paul

tried that paul just getting[57].00 which I wrong, but why all the rest of the time that I have input is correct look at the file above that I have put online please

Reply | Quote

try [h]:mm as the format using a colon instead of a period

Reply | Quote

I’ve just tried Paul’s suggestion and it displays as 57.00 (though when the focus is in that cell the contents are displayed above as 02/01/1900 09:00:00). Are you sure that you’ve entered the format correctly as he suggested?

X_LD, that doesn’t work as the OP requires. At least I get an answer of 1368:00….

Eliminate spare time: start programming PowerShell

Reply | Quote

hi zeddy it works by what you say I am still baffled why it does in the first place on just one line and not all of them

Reply | Quote

Hi mar

..in my attached file in post#17, it should work on all rows, and all relevant columns.

zeddy

Reply | Quote

Zeddy, how is this done? I’d like to know as I would like to learn more about Excel….

Eliminate spare time: start programming PowerShell

Reply | Quote

Hi access-mdb

..it works using the event code for the sheet (right-click on the sheet tabname, then select View Code)

Here is the code I used. It could be probably be improved, I just did it quickly:
Target is the cell that is changed (i.e. edited or entered) by the User.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub

zRow = Target.Row
zCol = Target.Column

If zRow  [h1].Column Then Exit Sub

zValue = Target.Value

Application.EnableEvents = False
Select Case zCol

Case [b1].Column, [c1].Column, [d1].Column, [g1].Column, [h1].Column

If zValue Like "#" Then Target = zValue & ":00"     'single digit assumed to be hour
If zValue Like "#." Then Target = zValue & ":00"    'assumed to be hour
If zValue Like "##." Then Target = zValue & ":00"   'assmed to be hour
If zValue Like "##" Then Target = "00:" & zValue    'assumed to be minutes

If zValue Like "#.#" Then               'treat as h:m0 e.g. 4.3 => 04:30
zHr = Left(zValue, 1)
zMin = Right(zValue, 1)
Target = zHr & ":" & zMin & "0"
End If

If zValue Like "#.##" Then              'treat as h:mm e.g. 6.25 => 06:25
zHr = Left(zValue, 1)
zMin = Right(zValue, 2)
Target = zHr & ":" & zMin
End If

If zValue Like "##.#" Then              'treat as hh:m0 e.g. 17.3 => 17:30
zHr = Left(zValue, 2)
zMin = Right(zValue, 1)
Target = zHr & ":" & zMin & "0"
End If

If zValue Like "##.##" Then             'treat as hh:mm e.g. 12.55 => 12:55
zHr = Left(zValue, 2)
zMin = Right(zValue, 2)
Target = zHr & ":" & zMin
End If

If zValue Like "####" Then              'assume to be hhmm e.g. 2015 => 20:15
zHr = Left(zValue, 2)
zMin = Right(zValue, 2)
Target = zHr & ":" & zMin
End If

If zValue Like "###" Then               'assumed to be hmm e.g 725 => 07:25
zHr = Left(zValue, 1)
zMin = Right(zValue, 2)
Target = zHr & ":" & zMin
End If


Case Else

End Select


Application.EnableEvents = True

End Sub

zeddy

Reply | Quote

I wondered if it was a bit of code, but I couldn’t find any. I wasn’t aware of that right click on the sheet tabname – was it there in Excel 2003? I’ve only just converted to 2013.

Eliminate spare time: start programming PowerShell

Reply | Quote

Hi

..yes, it’s been there for a long while.
..and it’s still there for 2016

zeddy

Reply | Quote