time formula

[illysten]

I have a spreadsheet containing time signatures (military hh:mm:ss) that I would like to have a description automated based on the time punch. If all times are listed in column A, then in column B any time punch before 08:00:00 listed as 'early', from 08:00:01 to 17:00:00 listed as 'ok' and anything after 17:01:00 listed as 'late'. I can't figure out the formula that would do that for me. Any suggestions?

[Sisyphus]

Hi,

Use the IF function like this:

Code: Select all

=IF(A10 < StartTime, "Eearly", "")


This formula will write "Eearly" or nothing in the cell it occupies, depending upon whether the time in A10 is smaller (earlier) than "StartTime". Make sure that A10 is formatted as Date (Rightclic/FormatCells/Number)

"StartTiume" is a reference, say 8:00 a.m.
a) You can enter the time directly into the formula - not so good
b) Place a cell, formatted as Date, at the top of the sheet and enter the start time there.
If you do this you can change the start time in future without needing to change any of the formulas.
Say, the start time is in cell B2, replace the word "StartTime" in the formula with $B$2.

You may also name B2 as "StartTime". Place the Cursor in B2. "B2" appears in the Name Box at the top left of your screen. Write StartTime in that box and press enter. You can now refer to B2 as StartTime anywhere in your workbook. The formula given above already refers to it. you can sue it without change. If you want to change the start time you just change the value in B2.

This should do the trick. Enjoy your evening.

[illysten]

Thanks for the info. I've tried that and i've come up empty. See below for example. A is what is pulled from the system. B i need it to automate what I manually typed in there based on the time. if it is before 08:00:00 and after 17:00:00 i would need it so say early or late respectivly.

When I try and run the formula as you described above and some variations, they all list as OK regardless of the time. I think it has to do with the date being attached, but I have been unable to seperate it. it'll come up as 01/00/1900 instead of disappearing like I want it to. Since i have about 40k lines to go thru, I need to automate as much of this as possible.

Thank you again for your help! it is much appreciated!

A B
Date/Time Time Frame
01/11/2011 16:40:34 OK
01/11/2011 16:42:11 OK
01/11/2011 17:04:27 Late
01/11/2011 17:05:48 Late
01/11/2011 17:06:41 Late
01/11/2011 17:07:13 Late
01/11/2011 17:07:57 Late
01/11/2011 17:17:46 Late
01/11/2011 17:18:30 Late
01/11/2011 17:19:19 Late
01/11/2011 17:19:40 Late
01/11/2011 17:20:07 Late
01/11/2011 17:20:26 Late
01/12/2011 07:18:10 Early
01/12/2011 07:19:40 Early
01/12/2011 07:24:16 Early
01/12/2011 07:26:51 Early
01/12/2011 07:28:18 Early
01/12/2011 07:33:15 Early
01/12/2011 09:22:19 OK
01/12/2011 09:23:04 OK

[Sisyphus]

Half of all problems with time and date formulae come from cell formatting. Therefore I won't dare judge a formula without seeing the formats of all affected or affecting cells, even if you would show us the formula you have used.
Why don't you post the worksheet with the trial data and the formula that doesn't work?
Regards,