Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 29th 06, 07:38 AM posted to microsoft.public.excel.misc
dk_ dk_ is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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

  #2   Report Post  
Old October 29th 06, 07:48 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old October 29th 06, 08:38 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old October 29th 06, 09:43 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old November 1st 06, 08:53 PM posted to microsoft.public.excel.misc
dk_ dk_ is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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   Report Post  
Old November 1st 06, 08:58 PM posted to microsoft.public.excel.misc
dk_ dk_ is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 07:53 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017