Thread: Formula Nightmare (for me) (MS Excel 2003)

1. Re: Formula Nightmare (for me) (MS Excel 2003)

Welcome to Woody's Lounge!

It would be nice if you could provide some examples of what you want to accomplish, in the form of a description and/or a sample workbook.

2. Re: Formula Nightmare (for me) (MS Excel 2003)

An example would certainly be helpful, it is a little tough to guess exactly what you want to test for. I can tell you that in Excel, time is kept as fractions of a day. Therefore, to test for 06:00, you check for .25, noon is .5, and 18:00 is .75. Also, with rounding error in the conversion to binary, you may need to round the values before testing. Also, make sure that the times are really Excel time values, not strings that look like times. If that does not help, please upload a workbook that shows what you want to do.

3. Formula Nightmare (for me) (MS Excel 2003)

I'm working on building a daily timesheet that each MDU (Mobile Data Unit) user in my work region can use easily. My hurdle has been in building a formula that will display one of four (4) different entries dependant on the 24 hour time format entry in an adjacent column. An example can easily be sent if need be. I have attempted a variety of "=IF" formulas, none of which have I been successful in recognizing the time format. Any and all help etc will be greatly appreciated. Am brand new to this site, so please be kind! ha

4. Re: Formula Nightmare (for me) (MS Excel 2003)

OK, I edited my first post by adding an attachment with a basic example of what I need the sheet to do. In essence, I need column "H" to show "9100" if any time between 7:30 & 16:00 is entered in column "F", "9150" for any time between 16:01 & 20:00 is entered in column "F", and "9200" for any time between 20:01 & 07:29 is entered. Hope this helps explain more what I'm trying to do.
Thanks!!
Steve

5. Re: Formula Nightmare (for me) (MS Excel 2003)

Thanks.

You can create a little auxiliary table with the time limits and corresponding pay codes:

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>1</td><td align=center>0:00</td><td align=center>9200</td><td align=center>2</td><td align=center>7:30</td><td align=center>9100</td><td align=center>3</td><td align=center>16:00</td><td align=center>9150</td><td align=center>4</td><td align=center>20:00</td><td align=center>9200</td></table>
You can then use this formula in H10:

=VLOOKUP(E10,\$A\$1:\$B\$4,2)

and fill down. See the attached version (I entered the formulas in column I so that you can compare the results with the one you entered manually).

6. Re: Formula Nightmare (for me) (MS Excel 2003)

Hans:
Thank you SOOOOOOOOOOOOOOOOOO much!! This is exactly the result I have been searching for!! I wish I'd found this site months ago. Again, thank you, your help is much appreciated!
Regards
Steve

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•