Enter Date and Time quick way

**kryptic** · 09-14-2013, 09:30 AM

Hi, I have four columns with Date, Shift Start time, Shift End time and Hours worked. I have many dates and times to enter but I would like to enter quick way without entering slashes and colons such as if I want to enter 9 September of current year, all I need to enter 909 and I should get "Mon 9 Sep 2013". And if I want to enter date of previous years, all I need to type 7812 and I should get "Tue 7 Aug 2012". For entering times, in Shift Start column I would like to enter 521 and I should get "5:21 PM" and in Shift End column I should get AM instead of PM. In the future, I might would like to change that to AM vice versa. For example AM for Shift Start and PM for Shift End. In Hours column, I would the number of hours worked by counting Shift End minus Shift Start. I can't enter so many dates and times by entering slashes and colons including AM or PM.

I am open for suggestion if its too short to enter date or time or if its not possible the way I want.

Thanks in advance.

**Jakobshavn** · 09-14-2013, 09:40 AM

So if you wanted the second of December you would enter:
212
and if you wanted the 21st of February you would enter:
212
??

**Tony Valko** · 09-14-2013, 09:41 AM

See this:

http://www.cpearson.com/Excel/DateTimeEntry.htm

**kryptic** · 09-14-2013, 11:02 AM

Jakobshavn, for 21st Feb it should be 2102 and for 9 September it is 909.

Tony Valko, I have seen that already but I don't know where those scripts goes to in Excel. Also it is American style date and I don't know how to change it to European style. That one looks pretty good though. I wish there was simple way just like entering 090913 should give you 09/09/13. I am okay with that as well.

**martindwilson** · 09-14-2013, 11:12 AM

090913 would work if
1 you format the column as text first
2 enter all your dates in that format
once done
select column
data tab
text to columns
next
next
choose date and select dmy from the drop down
click finish

**kryptic** · 09-14-2013, 11:22 AM

martindwilson, well for 090913 it didn't work. it worked for 200913. When entering 090913, it omits the initial 0 and becomes 90913 and stays that way. I am still thinking about entrying the quicker way as in cpearson.com. Can someone help me with Date and Time entry from cpearson.com please? I don't know where the code goes.

**martindwilson** · 09-14-2013, 11:28 AM

you must format column as TEXT FIRST before you enter anything or the leading 0 will disappear

**kryptic** · 09-14-2013, 12:00 PM

For date, I decided to format the column to Date and enter the initial date and then increase it by sequential order. So I think I don't need to manually enter every date as they are in sequential numbers anyway. Wish I thought about this earlier. But for shift start and shift finish time it is random numbers so I have to manually enter it in every cell. Now how can I get "5:21 PM" if I enter just 521? I want PM for shift start column and AM for shift end column to be added at the end and then I want to be able to calculate the number of hours worked in another column so I guess it can't be formatted as text as it cannot calculate.

**Tony Valko** · 09-14-2013, 01:48 PM

Originally Posted by kryptic

Can someone help me with Date and Time entry from cpearson.com please? I don't know where the code goes.

Select the sheet where this is to happen.

Right click the sheet tab and select View Code.

Paste the code into the window that opens.

Close the window to return to Excel.

You will probably have to edit the code to work on your specific range of cells.

Look for this line of code:

If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then

Adjust the Range portion to suit.

**kryptic** · 09-15-2013, 04:20 AM

That works like a charm. Only thing I have to do is type in 24 hours format to get Am, Pm right which is okay with me. Now which part of code do I need to change to remove the seconds? I just need the hour and minute.

**Tony Valko** · 09-15-2013, 09:30 AM

Assuming you want the time to display in 12hr format:

1:15 PM
1:15 AM
11:30 PM
11:30 AM

Either comment out or delete these lines of the code:

Please Login or Register  to view this content.

Change this line of code:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

**Tony Valko** · 09-15-2013, 10:05 PM

Here's the code with those changes applied. I also added a line that will clear the cell contents if an invalid time entry is made.

Please Login or Register  to view this content.

Change the range address to suit.

**kryptic** · 09-17-2013, 07:04 AM

Thanks very much. Now I am not able to calculate the number of hours worked from Shift Start and Shift End fields. If the Shift Start is 5:25 PM and Shift End is 4:15 AM, then I should be able to get 8.83 Hrs or 8 Hrs 50 Mins so that I can calculate the amount per hour.

Is there anyway I can change the code so that when I type in Shift Start, I should get PM only? I tend to type 5 instead of 17 as it is on paper but typing 5 gives me AM. As I quickly type, I cannot think and type whatever is on the paper.

**Tony Valko** · 09-17-2013, 10:09 AM

Originally Posted by kryptic

Thanks very much. Now I am not able to calculate the number of hours worked from Shift Start and Shift End fields. If the Shift Start is 5:25 PM and Shift End is 4:15 AM, then I should be able to get 8.83 Hrs or 8 Hrs 50 Mins so that I can calculate the amount per hour.

5:25 PM to 4:15 AM is 10hrs 50mins.

=MOD(end-start,1) formatted as h:mm = 10:50

=MOD(end-start,1)*24 formatted as General or Number = 10.8333333333

Is there anyway I can change the code so that when I type in Shift Start, I should get PM only? I tend to type 5 instead of 17 as it is on paper but typing 5 gives me AM. As I quickly type, I cannot think and type whatever is on the paper.

You can probably do that but I'm not much of a programmer and I don't know how to do it.

**kryptic** · 09-19-2013, 10:29 AM

I first entered all the shift starts and then shift ends column. That way you know where you are and type the correct time. I was able to quickly enter the times. It saved me a lot of time. Thanks for the help.

**Tony Valko** · 09-19-2013, 10:59 AM

You're welcome. Thanks for the feedback!

Enter Date and Time quick way

LinkBack

Thread Tools

Rate This Thread

Display

Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Re: Enter Date and Time quick way

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] automatically enter time & date

Macro to automatically enter time & date

Enter date and time

[SOLVED] Any quick key for entering Current Date and Time?

[SOLVED] Any quick key to insert current Date and Time?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions