PDA

View Full Version : [SOLVED] Time Format in a cell



GaryB
12-02-2014, 07:00 PM
Hi all,

I hope this is an easy question but it has me stumped. I have a spread sheet for tracking employee hours and I haven't been able to find a way for the colon to automatically load when I type in a time. What I am hoping to do is, for example, type in 0820 and have it load in to the cell as 08:20 Does anyone have a way to do this?

Thanks,

Gary

pike
12-02-2014, 11:21 PM
possibly a custom format ##":"##

GaryB
12-03-2014, 09:14 AM
Hi Pike,

Thanks you for the help. It does automatically put the colon in but instead of showing the input as time is now a number. so 10:00 is 1000. I still need it to show as time so I can calculate how many hours a person worked on their shift.

Thanks,
Gary

SamT
12-03-2014, 12:37 PM
Hey Gary, I just left the Stockton area Sunday.


I can't think of a way to get Excel to do what you want because when you type in a plain number, Excel stores the entry as a number, but displays it as formatted.

For example, if the Cell is formatted as "hh:mm:ss" and you enter .25, Excel will display 6:00:00

On the other hand, if you enter a formatted number String ("6:00:00") excel stores it as a number (.25 in this case) and displays it as a time (6
am and no minutes or seconds.)

Excel stores times as decimal fractions and dates as whole numbers. Date/times are stored as mixed numbers with fractions.

To see this, format one column as dates (03/14/02), one column as times (13:30:55) and one as date/time.

Enter the number 0.535455 into each column.
Enter the number 41976 into each column.
Enter 41976.535455 into each.

Now Enter the string "12-3-14" without the quote marks into any unformatted cell and copy it. In another unformatted cell Paste Special >> Values.

What Excel can't do, sometimes VBA can. If the times are always entered in the same Range of cells, VBA can "capture" the entries and change them to the desired "colonized" entries for you using using a Change Event Sub.

Tell us more about the workbooks this is happening in.

GaryB
12-03-2014, 01:29 PM
Hi Sam,

I see what you are saying. I was hoping there was some kind of work around. I have attached a sample of the excel file.

Thanks,

Gary

GaryB
12-03-2014, 02:17 PM
I have the solution for this and it works fine. I just need to forget about "time" and use the formula Pike supplied and set it up using military time and rewrite the formulas a bit and it works great. Problem solved, at least for my needs. Thank you Pike and Sam.

Gary

SamT
12-03-2014, 09:34 PM
:thumb

snb
12-04-2014, 12:40 AM
An alternative


Private Sub Worksheet_Change(ByVal Target As Range)
If IsDate(Format(Target, "@@:@@")) Then Target = Trim(Format(Target, "@@:@@"))
End Sub

GaryB
12-08-2014, 08:40 PM
An alternative


Private Sub Worksheet_Change(ByVal Target As Range)
If IsDate(Format(Target, "@@:@@")) Then Target = Trim(Format(Target, "@@:@@"))
End Sub

Hi Snb,

Thank you for this code. i thought I had this licked but i don't. The idea that Pike had was great, but, it treats the input like numbers not time so when I put the start and stop times in, depending on the input, I can end up with stuff like 7 hours and 80 minutes. Not good or usable. Will this code solve that problem?

Thanks,

Gary

snb
12-09-2014, 01:02 AM
Give it a try.

pike
12-09-2014, 01:31 AM
A lot of messing around but
with a helper column to convert all the time to decimal values =IF(LEN(a1)>2,LEFT(a1,LEN(a1)-2),0)+RIGHT(a1,2)/60
also add condition formating Rule not to allow value over 59 minutes =MID(A1,LEN(A1)-1,2)*1>59
then sum and convert base to a custom format =INT(SUM(B1:B7))&":"&(SUM(B1:B7)-INT(SUM(B1:B7)))*60

Toonies
12-09-2014, 05:11 AM
Hi I have had a look at this and this works

Format Time cells with


00\:00

and format total cells to


[hh]:mm

this formula should do the trick some Guru might be able to make it smaller

Total cell formula


=IF(COUNT(B4,C4)=2,MAX(0,MIN(TEXT(C4,"00\:00")+(TEXT(B4,"00\:00")>TEXT(C4,"00\:00")))-MAX(TEXT(B4,"00\:00"))),"00:00")+IF(COUNT(D4,E4)=2,MAX(0,MIN(TEXT(E4,"00\:00")+(TEXT(D4,"00\:00")>TEXT(E4,"00\:00")))-MAX(TEXT(D4,"00\:00"))),"00:00")

12600

Toonies

pike
12-09-2014, 11:43 PM
didnt know about the / in the custom format .. thats very helpful

GaryB
01-04-2015, 10:13 AM
Hi I have had a look at this and this works

Format Time cells with


00\:00

and format total cells to


[hh]:mm

this formula should do the trick some Guru might be able to make it smaller

Total cell formula


=IF(COUNT(B4,C4)=2,MAX(0,MIN(TEXT(C4,"00\:00")+(TEXT(B4,"00\:00")>TEXT(C4,"00\:00")))-MAX(TEXT(B4,"00\:00"))),"00:00")+IF(COUNT(D4,E4)=2,MAX(0,MIN(TEXT(E4,"00\:00")+(TEXT(D4,"00\:00")>TEXT(E4,"00\:00")))-MAX(TEXT(D4,"00\:00"))),"00:00")

12600

Toonies

This totally did the trick. I adjusted the code for the project and it works perfectly. I waited until he did a couple of payrolls to make sure and he is one happy person. Thank you for this and to everyone thank you for all your responses and help. Happy New year.

Gary

Toonies
01-04-2015, 11:52 AM
Hi Gary glad it helped you out.

I simply adapted the original code (I believe its a DaddyLongLegs formula)