Solved: Custom Format hh:mm producing odd result?

[Simon Lloyd]

Hi all, i am creating a time sheet - simple enough!, but when i try to custom format the cells to hh:mm i cannot enter 00:00 as it displays blank or if i enter any number without entering :00 after it again it displays 00:00!

Once the cell is formatted if i was to enter lets say 13 the formula bar shows

13/01/1900 00:00:00

any ideas how to cure it?, i have tried a number of things but to no avail!

Regards,
Simon
Excel 2003 Office Pro

[Ken Puls]

Hi Simon,

Remember that dates are numbers in Excel, and times are fractions of numbers. (i.e. .5 is a half day)

What you get when you just enter a time is that it defaults back to a Jan 1, 1900 date, unless you specify the whole day. (You enter 6:00 AM, and Excel converts it to .25. .25 equates to Jan 1, 1900 at 6:00 AM.)

One way you could try to fix the issue is by code, although I'm sure someone will come up with a non-code way to do it. Using a worksheet_change event:
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value < 1 Then Target.Value = Target.Value + Int(Now())
End Sub[/vba]

Basically, it adds today's day to any time entered that defaults to under 1 (less than a full day). This obviously affects all cells on the sheet, so you'd want to modify it to only apply to certain ranges. It always adds today's day too, so if you wanted it to be yesterday, you'd have to edit if after the fact.

HTH,

[Simon Lloyd]

Thanks for the explanation Ken, i find if i find if i have it formatted as hh:mm i can enter the time explicitly like this 13:00 and the formula bar then displays 13:00, it highly annoying that you cant simply type 13 and it fill in for you just as you could if the cell was formatted currency!

As it happens the times will be filled in from a userform listbox so will be sent to the cell in the correct format, however if they need to be edited later then the user (my girlfriend!) will find that the cell will behave oddly, i will incorporate your suggestion for my range, but, i have several sheets that have the Time element in them so is it possible to set up a global target value for any cell formatted as time? or perhaps a class module to handle this?, i suppose the tricky problem would be that the times to not appear in the same columns on each sheet!

As you can see i have dumped trying to use access to build her a small business package that will look after her clients/staff/jobs/accounts and have turned to Excel although far from proficient i am more comfortable and better equipped to construct one!

Regards,
Simon

[Simon Lloyd]

Ken i know you are very adept at what you do but rather than Now in the statement should it be Time?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value < 1 Then Target.Value = Target.Value + Int(Now())
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value < 1 Then Target.Value = Target.Value + Int(Time())
End Sub

because if i use the one you supplied it actually adds the date to the cell as well as the time but the latter just displays the time?

Regards,
Simon

[Ken Puls]

LOL!

Nope. Int(Now()) returns the whole number for today's date. (i.e. 39,197). Int(Time()) will return 0. Int truncates all decimal places, you see.

Remember that the goal was to convert your time to the time on today's date. For this reason I was adding today's date value to the fractional value you had which was referring to a 1/1/1900 date.

Does that make sense?

EDIT: Format it as time again, and it should display correctly as just a time.

[Ken Puls]

Okay, back to your above question...

Once you have the value in the cell, and it's formatted correctly, your girlfriend should be able to edit it by clikcing on the cell and editing the formula. What I would do is this:

Since you are inputting from a userform, make the userform adjust the dates before you slap them in the cell, to ensure that they are fractions of today's time. (Or whatever day you want.)

Then apply data validation to the target cells, making sure that the user cannot enter dates less than a week old (or whatever period you want to edit for.) Make sure you use Decimal as the validation type, as you want to allow fractional days.

What this accomplishes is that your userform converts things. The data validation is not trigged when the amount is placed by code, but when she goes to edit, it will ensure that the dates are still valid.

Does that make sense?

[Simon Lloyd]

Ken, i may well be confusing the issue here....poor explanation perhaps, so attached is an example!

Regards,
Simon

[Bob Phillips]

.

[Bob Phillips]

Better

[Simon Lloyd]

Ah the great El Xid!, long time since i've heard from you, thanks for going to the time and trouble for that, but there must be something wrong with my PC if i enter a figure lets say just 13 (denoting 13:00) it just displays 00:00 if i enter a time like 0300 it displays 00:00, if i check the formula bar it shows a date format and the time 00:00......it does work better i must admit, is there a way where Susan could select the cell and type a time in string format i.e 2045 and then the cell reformat to 20:45.

To be honest though it shouldn't be a problem as all times will be passed in that format from a UF, it's only if amendments have to be made manually!

Anyway hows you 'n' yours? been jetsetting lately?

Regards,
Simon

[Bob Phillips]

This should be better.

Anyways, we are all good, you? Just had a weekend break in Prague, the weather was fabulous, did nothing but sit in cafes and eat and drink all day. Off to the States again next week.

[Simon Lloyd]

Prague! fantastic i've always wanted to go there....and im still slimming to get in your suitcase for that one!, as for me i have just come back from a corporate golf day at De Vere Hotels, due to the fact that i play like i'm blindfolded they have agreed to redefine what "in the rough" means!, we had a trickshot demo from Dave Edwards absolutly fantatsic!, he can make the ball go where he wants when he wants how he wants and from any angle, height or position! truly amazing!

As for your amendment to the code....don't sweat it any more over the code as i could have a userform pop up to make the changes and they will always be in the desired format - your amended sheet has some anomalies - enter 1 and it shows 10:00 enter 5 and it shows 02:00, enter 324 and the time shows 08:40! odd eh?

I have absolutely tons to do with this as it is going to be the hub of her running her business - in the big scheme of things this is a minor problem!

Regards,
Simon

[Bob Phillips]

Maybe this

[Simon Lloyd]

Not one to be beaten eh?, thank you again, works like a charm!, while you have the worksheet.....(being cheeky now!), if you remove the time from G2 or H2 L2 & M2 will show #Value and i have tried using if(iserr and if(I2="" etc but they still show #Value if G2 or H2 are blank?

Regards,
Simon

[Bob Phillips]

Try again

[Simon Lloyd]

Bob.....was it really that simple to fix the formula moving the *24? after all the things i tried i didnt even see that it was still trying to multiply "" by 24!

I think that this

Me.Range(Me.Cells(.Row-1, "I"), Me.Cells(.Row-1, "N")).AutoFill _
Me.Range(Me.Cells(.Row-1, "I"), Me.Cells(.Row-1, "N")).Resize(2)

was a clever forethought of yours!, i made a slight mod removing the -1 as the first entry copied the titles so every line copied the titles.....but still i love that concept of only having the formula in when needed, it should help keeping the size of the workbook down.

You know that with me venturing on another large project (well large by my standards) that questions will be thick and fast! (mosly thick!)

Kind regards,
SImon

[Bob Phillips]

i made a slight mod removing the -1 as the first entry copied the titles so every line copied the titles.....but still i love that concept of only having the formula in when needed, it should help keeping the size of the workbook down.

What I tend to do in this type of case is insert a row at say row 2 with the formulae, and hide it. Then when they input in any row, it copies down from above, and never get infected by titles.

[Bob Phillips]

Bob.....was it really that simple to fix the formula moving the *24? after all the things i tried i didnt even see that it was still trying to multiply "" by 24!

No I think I also changed tests against 0 to test against "".