Making a time sheet: any one could help

[wiL]

Hey guys I made a time sheet. In this time sheet i made a macro where when I press ctrl+t it fills in the date in a column and the time in the column next to it. So to start my day I would press ctrl+t and to end it i would goa column over and repeat the macro. and then i made it so that in the fifth column it adds the 2 times together with this formula "=IF(E27>0,(IF(G27>E27, G27-E27, 1+G27-E27)),"")" formula works fine unless you make a "mistake" and change the ending time manually to a time that is early then the starting time but on the same day. So I wanted to see if there is anyway of the total time column can return an error message if the ending time is earlier then the starting time by looking at the date in both date columns. Im going to attach the workbook to this post. on the tabs on the bottom it is under time sheet. and i think the macros should transfer over too. ctrl+t is the time macro if you want to try it out. I may have not been so clear on explaining it so ask any questions if that is true.

[davesexcel]

No macro is required to enter time, just use Ctrl&Shift&;, this combo will automatically put in the time.

Basically Ctrl & : is the correct way, most people explain it as Ctrl&Shift&;

[teylyn]

Im going to attach the workbook to this post.

When?

If you want to make sure that a certain time value is greater than another cell value, you can use data validation. If B1 must be greater than A1 the use data validation with the Custom option and put this formula in

=B1>A1

Set up a friendly error message to help the user out.

hth

[wiL]

Dave I know about that macro but I wanted to make it so that the date and the time show up in 2 different cells so that I can get the total time in the end. So what the macro I made did was filled in the date in the cell that was selected moved over a cell to the right and input the current time. If you have time look at the workbook. its the time sheet on the tab on the bottom left. I think that data validation thing might work but it wont take into consideration the date which would be kind of nice. maybe it would with an if statement. i duno how to structure that though

[wiL]

I don't even know how you go about setting a data validation in those cells with that formula you specified. and right now Ive worked for the past 12 hours in construction now am at this second job and im just not thinking straight right now.

[davesexcel]

I like teylyn's idea, it works very well,
Highlite, G27:H54
Go to Data Validation, custom, enter this formula
=E27>G27

[teylyn]

What davesexcel suggests can be put into practice without any macros.

select first cell and hit Ctrl ; ( cell formatted as date)
select next cell and hit Ctrl : (or Ctrl-Shift ; as per Dave's explanation, cell formatted as time)

To set up data validation, select the cell, click Data - Validation - custom and enter a formula along the lines of the one I suggested above. If an invalid date is entered, the user will get an error message, which you can customise to provide information about the expected input.

hth

[wiL]

Yeah oops i remember I had do do that same data validation with the previous document with the offset command and blah blah but what im asking is lets say we are recording something in our studio and start at 11:30 on 12/2/09 and then continue it till 12:30 Am on 12/3/09. What would I do so that the total column looks at both the date and the time and figure out the difference between the starting and ending time. The formula I have in the total right now is. =IF(E27>0,(IF(G27>E27, G27-E27, 1+G27-E27)),"") which does the job but someone else isn't satisfied with it. They want it so that if you it looks at the date and time and then finds the difference. But I don't think that's possible so I wanted to get a second opinion on this issue. As for the macros I understand what your saying but the way I have is the way he wants to keep it. SO, basically all I want know is if there is formula for what I'm trying to end up with in the end. If its not to much of a hassle check my workbook and see how it works and then let me know what you think

[wiL]

btw it doesnt let me have both data validation and a formula in one cell

[wiL]

never mind i screwed up data validation does work with formulas.

[teylyn]

Hi,
in a table like this

Please Login or Register  to view this content.

the formula for the time difference is

=(C2+D2)-(A2+B2)

hth

[wiL]

that formula returns 0:0 for me

[teylyn]

check again.

A2 = 4/12/2009
B2 = 11:12 a.m.
C2 = 4/12/2009
D2 = 11:22 a.m.
E2 = (C2+D2)-(A2+B2) = 0:10:00

[wiL]

It still doesn't work for multiple days though... that's the whole issue I've been trying to get across. Have you looked at my work book at all? lol I already have a formula that does that and a little bit more