Excel Question #46

[Betty]

Originally Posted by Alex

Oh there were two pictures.

Ok, in cell G5 the problem is that you have the SUM going C4:F4 instead of B4:F4. With what you have you end up asking "what is the minimum of 8, 8, and 8).

Once you fix that sum you're going to get 0 in G5.

Cell G5 is =IF(SUM(C5:F5)<40,MIN(MIN(8,40-SUM(C5:F5)),G4),0)

I'm not seeing C4:F4 - or am I missing something.

[Moonliner]

Originally Posted by Betty

Cell G5 is =IF(SUM(C5:F5)<40,MIN(MIN(8,40-SUM(C5:F5)),G4),0)

I'm not seeing C4:F4 - or am I missing something.

Cell G5 should read: is =IF(SUM(B5:F5)<40,MIN(MIN(8,40-SUM(B5:F5)),G4),0)

What is the formula for cell G6 and G7?

[Betty]

Originally Posted by Moonliner

Cell G5 should read: is =IF(SUM(B5:F5)<40,MIN(MIN(8,40-SUM(B5:F5)),G4),0)

What is the formula for cell G6 and G7?

There we go! Yay!

[Moonliner]

Originally Posted by Betty

There we go! Yay!

You probably already figured this out, but you need to make the same change to cells G13 and G21

[Betty]

Originally Posted by Moonliner

You probably already figured this out, but you need to make the same change to cells G13 and G21

Thanks.

[Moonliner]

I was assigned a "high priority" task today. A "You are to work on nothing but this for the new few days" thing.

The task is to match up some data in a couple of overly large excel spreadsheets.

So I imported the data into SQL Server and created a couple of cross table queries to pull the needed data and drop it back into excel. Total time: ~45min.

So my question is, what's good on Netflix? I have a few days to kill.....

[Kevy Baby]

I have a spreadsheet that can only be downloaded as a tab delimited file. One of the fields is a date/time field that comes in as text in this format:

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

Obviously I cannot just format it as a date. The shortest way I have found to get this converted to a date is (assuming the source data is in column A):

1. Enter this function in column B: =LEFT(A2,11) (the date portion is always 11 characters)
2. Enter this function in column C: =DATEVALUE(B2)
3. Copy then Paste Value of Column C

Yes, I will combine the two functions into one: I left them separate to make the example easier to follow. Also, there are several hundred rows of data and the formula will need to be filled down.

So
1. Is there a simpler way to do this in Excel? I need to be able to sort/group/etc. the data by dates and other data in the spreadsheet (daily summary of debits and credits by company name in a monthly report)
2. Having never worked in Macros (but always willing to learn something new) how difficult is this to script so that the CSV file can be automatically processed once per month? I want to be able to hand off to our accounting department to handle (I could manually do this every month myself in probably less time than I would spend writing and testing said macro, but I would rather automate and give to someone else to handle).

And I cannot change the report as it is provided: I gotta work with what I have.