Prevent # in time cell

[dprather6of10]

First, thanks for all everyone does here. I've found answers before this, but not seeing what I'm hunting for, so I'm a 1st time poster.

I created a time sheet for folks that are accustomed to entering time as a number (6.5 hours vice 6:30 in [h]:mm format). I standardized to time format across that time entry sheet and the database that runs both employee salary and client billing. We are 1.5 months into it and we still routinely get employees making occasional mistakes. We created a manual correction process before running bills, but I wanted to see if there was a way to automated preventing the error in the first place.

Any help is appreciated.

Thanks.

 

[FishingIsMoreFun]

What error?

Someone else might have to answer this as I am off to work.

Though you have not explained the error and are you cells formatted to date/time or number?

Cheers

 

[dprather6of10]

Thanks Fishing.

Every time cell is formatted to custom time ([h]:mm specifically). When the users accidentally enter a decimal, the formula results are off. The formatting remains time though.

I get no Excel errors, just the wrong answer in calculations.

To demonstrate,
A1 = 3:30, formatted custom [h]:mm
B1 = $10
C1 = =(A1/0.041666667)*B1. The division allows for multiplying time with $ to get $/hr

Result is $35, which is right.

Now type 3.5 into A1... The formatting converts it to 84 hours (3x24 + 12). You can change the formatting of A1 to number to show 3.5, which some of the users do, but the answer still remains $840.

I can use an if statement in C1 to apply the right multiplication for this example, but 1) I don't know how to check for either the colon or period and 2) leaving the value as a decimal throws off other calculations (like totals) in pivot table reports. I would much rather ensure the input is right.

By the way. They requirement was to standardize to time vice decimal, so I didn't have a choice.

Thanks again.

 

[FishingIsMoreFun]

The time entered is 3:30 which means 3 hours and 30 minutes.

But if a user tries to enter a decimal like 3.5 to mean 3 1/2 hours, then you have it converted to 3:30, how do you know the user didn't make an error and meant to enter 3:50?

So to automatically convert between the two could also be adding to error.

What you most probably want is a VBA function that will only accept the
[h]:mm format.

 

[CWatts]

Along the lines of what FIMF reccommended, perhaps you could use a user form, where there will be two boxes: one for hours and one for minutes, with a colon displayed between them so there is no confusion about the approrpirate entry method.

You could then have validation on your submit button to make sure it conforms such as hours from 0-23, and minutes from 0 to 59. You could also have an AM/PM drop down if you wanted to give that option.

Alternatively, you could also have another section in case the user wants to put in decimal hours so they have that as an option.

 

[dprather6of10]

Thanks for the thoughts and ideas. I hadn't thought of separating the hours from the minutes and will discuss with the client. That would be the best solution I think.