Help!

Using military time but with 100 minutes not 60 minutes

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Excel General (archive) RSS
Next:  Templates - help 43  
Author Message
srwamp
External


Since: Nov 20, 2005
Posts: 3



PostPosted: Sun Nov 20, 2005 2:39 pm    Post subject: Using military time but with 100 minutes not 60 minutes
Archived from groups: microsoft>public>excel>misc (more info?)

Is it possible to convert regular time into military time. But instead of
using 60 minutes using 100 minutes. Ex. typing in 14.30 and it changes to
14.50(which is using 100 minutes.) after you click enter or go to the next
cell. I use this in my work to help figure easier how many hours my drivers
have worked.
Back to top
David McRitchie
External


Since: Jul 28, 2005
Posts: 1688



PostPosted: Sun Nov 20, 2005 7:02 pm    Post subject: Re: Using military time but with 100 minutes not 60 minutes [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Military time with 100 minutes to the hour? Privatization?
Been watching TV (well it's on anyway) and there were no earth shaking
anouncements.

You might take a look at Chip Pearson's
http://www.cpearson.com/excel/DateTimeEntry.htm
Back to top
Roger Govier
External


Since: Sep 09, 2005
Posts: 1005



PostPosted: Sun Nov 20, 2005 7:15 pm    Post subject: Re: Using military time but with 100 minutes not 60 minutes [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi

Time is stored in Excel as fractions of a day.
Multiply your time value by 24, and it will turn to decimal so 14:30 will be
14.5


Regards

Roger Govier


srwamp wrote:
> Is it possible to convert regular time into military time. But instead of
> using 60 minutes using 100 minutes. Ex. typing in 14.30 and it changes to
> 14.50(which is using 100 minutes.) after you click enter or go to the next
> cell. I use this in my work to help figure easier how many hours my drivers
> have worked.
Back to top
Roger Govier
External


Since: Sep 09, 2005
Posts: 1005



PostPosted: Sun Nov 20, 2005 7:15 pm    Post subject: Re: Using military time but with 100 minutes not 60 minutes [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Sorry,
Forgot to add, Format the cell with the formula as General, or Number with
two decimal places.

If your time as 14:30 is in A1, then B1 = A1*24
B1 need to be formatted as described.

Regards

Roger Govier


Roger Govier wrote:
> Hi
>
> Time is stored in Excel as fractions of a day.
> Multiply your time value by 24, and it will turn to decimal so 14:30
> will be 14.5
>
>
> Regards
>
> Roger Govier
>
>
> srwamp wrote:
>
>> Is it possible to convert regular time into military time. But instead
>> of using 60 minutes using 100 minutes. Ex. typing in 14.30 and it
>> changes to 14.50(which is using 100 minutes.) after you click enter or
>> go to the next cell. I use this in my work to help figure easier how
>> many hours my drivers have worked.
Back to top
Bryan Hessey
External


Since: Nov 20, 2005
Posts: 1



PostPosted: Sun Nov 20, 2005 9:21 pm    Post subject: Re: Using military time but with 100 minutes not 60 minutes [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Would it not be easier to work with cells defined as [h]:mm:ss, which
would give easy display of recognised time formats, ie :15 (not .25)
for a quarter hour?

This 'improper' time format supports most required functions.

(Just a thought)


srwamp Wrote:
> Is it possible to convert regular time into military time. But instead
> of
> using 60 minutes using 100 minutes. Ex. typing in 14.30 and it changes
> to
> 14.50(which is using 100 minutes.) after you click enter or go to the
> next
> cell. I use this in my work to help figure easier how many hours my
> drivers
> have worked.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=486678
Back to top
srwamp
External


Since: Nov 20, 2005
Posts: 3



PostPosted: Mon Nov 21, 2005 4:32 am    Post subject: Re: Using military time but with 100 minutes not 60 minutes [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Mr. Govier,

Thank you for responding back. But I would like the time 14.30 to change to
14.50 in the same cell. Meaning if I put 14.30 in A1 I would like it to
change to 14.50 in A1. Is that possible.

"Roger Govier" wrote:

> Sorry,
> Forgot to add, Format the cell with the formula as General, or Number with
> two decimal places.
>
> If your time as 14:30 is in A1, then B1 = A1*24
> B1 need to be formatted as described.
>
> Regards
>
> Roger Govier
>
>
> Roger Govier wrote:
> > Hi
> >
> > Time is stored in Excel as fractions of a day.
> > Multiply your time value by 24, and it will turn to decimal so 14:30
> > will be 14.5
> >
> >
> > Regards
> >
> > Roger Govier
> >
> >
> > srwamp wrote:
> >
> >> Is it possible to convert regular time into military time. But instead
> >> of using 60 minutes using 100 minutes. Ex. typing in 14.30 and it
> >> changes to 14.50(which is using 100 minutes.) after you click enter or
> >> go to the next cell. I use this in my work to help figure easier how
> >> many hours my drivers have worked.
>
Back to top
srwamp
External


Since: Nov 20, 2005
Posts: 3



PostPosted: Mon Nov 21, 2005 4:32 am    Post subject: Re: Using military time but with 100 minutes not 60 minutes [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Mr. Govier,

Thank you for responding back. But I would like the time 14.30 to change to
14.50 in the same cell. Meaning if I put 14.30 in A1 I would like it to
change to 14.50 in A1. Is that possible.

"Roger Govier" wrote:

> Sorry,
> Forgot to add, Format the cell with the formula as General, or Number with
> two decimal places.
>
> If your time as 14:30 is in A1, then B1 = A1*24
> B1 need to be formatted as described.
>
> Regards
>
> Roger Govier
>
>
> Roger Govier wrote:
> > Hi
> >
> > Time is stored in Excel as fractions of a day.
> > Multiply your time value by 24, and it will turn to decimal so 14:30
> > will be 14.5
> >
> >
> > Regards
> >
> > Roger Govier
> >
> >
> > srwamp wrote:
> >
> >> Is it possible to convert regular time into military time. But instead
> >> of using 60 minutes using 100 minutes. Ex. typing in 14.30 and it
> >> changes to 14.50(which is using 100 minutes.) after you click enter or
> >> go to the next cell. I use this in my work to help figure easier how
> >> many hours my drivers have worked.
>
Back to top
Roger Govier
External


Since: Sep 09, 2005
Posts: 1005



PostPosted: Mon Nov 21, 2005 2:41 pm    Post subject: Re: Using military time but with 100 minutes not 60 minutes [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi

No, a formula cannot alter the format of a cell. It will only work as
described. If you want the input to be altered on entry, then you would need
a change event macro.
David has already given you a pointer to Chip Pearson's site where you will
find code for handling different types of input.
Plagiarising a bit of that code to suit your needs, and without lots of
error checking, the following will convert data as you wish in the range
A1:A100. Amend to suit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo endmacro
If Application.Intersect(Target, Range("A1:A100")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
Target.Value = Target.Value * 24
Target.NumberFormat = "0.00"

endmacro:

Application.EnableEvents = True
End Sub

To paste the code into your workbook, right click on the sheet tab where you
do your data entry and paste the code into the white pane on the right.


Regards

Roger Govier


srwamp wrote:
> Mr. Govier,
>
> Thank you for responding back. But I would like the time 14.30 to change to
> 14.50 in the same cell. Meaning if I put 14.30 in A1 I would like it to
> change to 14.50 in A1. Is that possible.
>
> "Roger Govier" wrote:
>
>
>>Sorry,
>>Forgot to add, Format the cell with the formula as General, or Number with
>>two decimal places.
>>
>>If your time as 14:30 is in A1, then B1 = A1*24
>>B1 need to be formatted as described.
>>
>>Regards
>>
>>Roger Govier
>>
>>
>>Roger Govier wrote:
>>
>>>Hi
>>>
>>>Time is stored in Excel as fractions of a day.
>>>Multiply your time value by 24, and it will turn to decimal so 14:30
>>>will be 14.5
>>>
>>>
>>>Regards
>>>
>>>Roger Govier
>>>
>>>
>>>srwamp wrote:
>>>
>>>
>>>>Is it possible to convert regular time into military time. But instead
>>>>of using 60 minutes using 100 minutes. Ex. typing in 14.30 and it
>>>>changes to 14.50(which is using 100 minutes.) after you click enter or
>>>>go to the next cell. I use this in my work to help figure easier how
>>>>many hours my drivers have worked.
>>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Excel General (archive) All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum