Excel Question #46

[Moonliner]

Originally Posted by Betty

That's a good question - let me verify. (I'm pretty sure the wording "work week" doesn't mean any old days.)

With the one change made to cell G5 it seems to work fairly well.

[Betty]

Here's a link to the file: http://hemetgraphics.com/ot.zip

[Alex]

Though the rules given above never mention any trigger of overtime at 40 hours so I'm not sure why that is appearing in the formulas.

[Moonliner]

Originally Posted by Alex

Though the rules given above never mention any trigger of overtime at 40 hours so I'm not sure why that is appearing in the formulas.

That is a good question, I assume it's because five 8 hour days = 40 hours.

[Betty]

I'm still waiting for verification on the question but I'm 99% sure that it's working the way it should.

Thank you so much! really and truly. On a day where I've been unusually busy, had numerous car issues and a few other things pop up, you have helped tremendously.

[Moonliner]

Originally Posted by Betty

I'm still waiting for verification on the question but I'm 99% sure that it's working the way it should.

Thank you so much! really and truly. On a day where I've been unusually busy, had numerous car issues and a few other things pop up, you have helped tremendously.

Glad to help.

[Alex]

I can't see the spreadsheet from work but would be interested in seeing the formulas in the other cells.

But if it is working, then good enough.

[Alex]

This will result in losing the time portion of the cell but doesn't require any formulas and would be a very simple macro to record.

If so, then you can do this and not mess with any formulas. Assumes the timestamp is in column A. Easy macro to record if you want.

1. Select Column A
2. Set Number format of Column A to whatever format you want to see the date expressed in. For example purposes pick 3/14/01.
3. With Column A selected (to avoid collateral damage in other columns) open the Search and Replace dialog (Ctrl-H).
4. In the "Find what" box enter " *" without the quotes (that is: <space>*). This will tell Excel to find the first instance of a space in the cell and then replace that space and everything to the right of it with what comes next.
5. In the "Replace with" box enter nothing at all.
6. Click "Replace all" button.

This will take a cell that says

26-Jun-2014 10:11:13 AM PDT

and essentially truncate the value at the first space. This truncated form is one that the date formatting recognizes and so you'll now see

6/26/14

You can then sort, pivot, use in formulas, etc., however you want.

[Alex]

Oh, and if you want to keep the time part of the time stamp around, before you do the above, copy Column A into a blank column (let's say Column B).

Do the above to Column A. Then in Column B things are a bit trickier to get rid of the date (because for some stupid reason when you want to trim tot he left the expression match starts from the right so just finding spaces will match to the one before PDT).

But these could clear out the date part if you need:

If all timestamps are from 1/1/2000 and later:

Find what: "*20?? " without quotes: *20??<space>

This essentially says "find the pattern 2 0 any character any character space and then trim that pattern and everything to the left of it."

If all timestamps are from the 1900s and 2000s:

Find what: "*20?? " without quotes: *20??<space>

then repeat with:

Find what: "*19?? " without quotes: *19??<space>

[Alex]

Kevy better be dead to explain him not coming back to thank us vociferously for being such Excel badasses.

Or just let us know that it filled his need.