A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Is there a way to enter times without typing a colon?



 
 
Thread Tools Display Modes
  #1  
Old October 29th 06, 07:38 AM posted to microsoft.public.excel.misc
dk_
external usenet poster
 
Posts: 70
Default Is there a way to enter times without typing a colon?

It there a way to enter times without 'typing' a colon in an empolyee
time sheet, and still be able to add the number of hours worked?

....For example, type 0930 and 2130 instead of having to type either 9:30
or 09:30 and 21:30, and still be able to do the math on figuring the
number of hours worked in a day?

Thanks.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture
Ads
  #2  
Old October 29th 06, 07:48 AM posted to microsoft.public.excel.misc
David Biddulph
external usenet poster
 
Posts: 618
Default Is there a way to enter times without typing a colon?

=TIME(INT(A1),100*MOD(A1,1),0)
--
David Biddulph

"dk_" > wrote in message
...
> It there a way to enter times without 'typing' a colon in an empolyee
> time sheet, and still be able to add the number of hours worked?
>
> ...For example, type 0930 and 2130 instead of having to type either 9:30
> or 09:30 and 21:30, and still be able to do the math on figuring the
> number of hours worked in a day?
>
> Thanks.
>
> -Dennis



  #3  
Old October 29th 06, 08:38 AM posted to microsoft.public.excel.misc
MartinW
external usenet poster
 
Posts: 860
Default Is there a way to enter times without typing a colon?

Hi Dennis,

One way

A1 0930 (Start time)
A2 1430 (Start lunch)
A3 1500 (End Lunch)
A4 2130 (Finish time)
A5
=((CEILING(A4,25)-CEILING(A1,25))-((CEILING(A3,25)-CEILING(A2,25))))/100

This will round to the nearest quarter hour.
You will need a different approach if your shift
crosses midnight.

Format A1 to custom 0000

HTH
Martin


  #4  
Old October 29th 06, 09:43 AM posted to microsoft.public.excel.misc
Niek Otten
external usenet poster
 
Posts: 3,440
Default Is there a way to enter times without typing a colon?

Hi Dennis,

http://xldynamic.com/source/xld.QDEDownload.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"dk_" > wrote in message ...
| It there a way to enter times without 'typing' a colon in an empolyee
| time sheet, and still be able to add the number of hours worked?
|
| ...For example, type 0930 and 2130 instead of having to type either 9:30
| or 09:30 and 21:30, and still be able to do the math on figuring the
| number of hours worked in a day?
|
| Thanks.
|
| -Dennis
|
| --
| Dennis Kessler
| http://www.denniskessler.com/acupuncture


  #5  
Old November 1st 06, 08:53 PM posted to microsoft.public.excel.misc
dk_
external usenet poster
 
Posts: 70
Default Is there a way to enter times without typing a colon?

The code on Chip Pearson's page, (link below), was exactly what I was
looking for. Sorry if I didn't ask my question properly.

....From Ron Coderre's post to another similar question:

> See Chip Pearson's website for options:
>
> http://www.cpearson.com/excel/DateTimeEntry.htm
>
> Does that help?
> ***********
> Regards,
> Ron



-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture



In article >,
"Niek Otten" > wrote:

> Hi Dennis,
>
> http://xldynamic.com/source/xld.QDEDownload.html
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "dk_" > wrote in message
> ...
> | It there a way to enter times without 'typing' a colon in an empolyee
> | time sheet, and still be able to add the number of hours worked?
> |
> | ...For example, type 0930 and 2130 instead of having to type either 9:30
> | or 09:30 and 21:30, and still be able to do the math on figuring the
> | number of hours worked in a day?
> |
> | Thanks.
> |
> | -Dennis
> |
> | --
> | Dennis Kessler
> | http://www.denniskessler.com/acupuncture

  #6  
Old November 1st 06, 08:58 PM posted to microsoft.public.excel.misc
dk_
external usenet poster
 
Posts: 70
Default Is there a way to enter times without typing a colon?

Yep, that works.

Thank you.

I'll study the formula and try to figure it out.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture


In article >,
"David Biddulph" > wrote:

> Sorry, my error. That formula was one I'd given as answer to someone who
> wanted to enter number in the format 09.30. For your format, it needs to be
> =TIME(INT(A1/100),100*MOD(A1/100,1),0)
> --
> David Biddulph
>
> "dk_" > wrote in message
> ...
> > David,
> >
> > I copied your formula in B1.
> >
> > I entered 0930 in A1
> >
> > The result was 6:00 PM in B1
> >
> > I don't see what this formula should do.
> >
> > I was trying to find a way to enter 0930 or 930, with no colon to be
> > computed in a formula as 9:30 AM.
> >
> > Thanks.
> >
> > -Dennis
> >
> >
> > In article >,
> > "David Biddulph" > wrote:
> >
> >> =TIME(INT(A1),100*MOD(A1,1),0)

> >
> >
> >
> > --
> > Dennis Kessler
> > http://www.denniskessler.com/acupuncture

>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I enter a date typing 040906 and have it appear 04/09/06? I Stover New Users to Excel 3 September 12th 06 01:44 PM
Why do I lose hundredths when summing elapsed times in a pivot tab pbv Excel Discussion (Misc queries) 1 August 8th 06 06:31 AM
Avg Times in MOD fx using selected rows vldavis809 Excel Worksheet Functions 2 July 7th 06 10:08 AM
typing addresses & when I hit enter need to be on next line cel B Tina Excel Discussion (Misc queries) 2 June 30th 06 08:52 AM
How do I enter minutes and seconds (lap times)? SeanP Excel Discussion (Misc queries) 1 January 18th 05 10:54 PM


All times are GMT +1. The time now is 12:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.