Calling Excel programmers

[mickthetree]

ah I see.

Google checkout does have an API, so you could possibly write something to do it, or get someone to do it for you, but that is probably a bit overkill for this.

Good luck with it.

 

[matt]

Does Google check not allow you to export the data?

 

[matt]

You could, of course, paste to a text doc and then import in to Excel. Adjust the cell separators to omit the bits that you don't want.

separate day month and year in to cells and then concatenate using =B1&" "&A1&" "&C1 (to get the cells in to the order DD MMM YYYY which will match a custom date format). Which can then be used in calculations.

 

[Jake]

You can use find/replace to convert "1 Jan," to "01 Jan" then repeat through to 9 Jan, that gets rid of your variable length problem.

edit: bit boring with all 12 months!

edit again, oh OK its Jan 1, so 'space1,' for "space01" does the lot.

 

[RogerS]

Where does LEN come in, Steve?

 

[SimonB]

Assuming dates are always in either of these two forms

MMM D, YYYY or MMM DD, YYYY

then this should work

=MID(A1,5,SEARCH(",",A1)-5)&"/"&LEFT(A1,3)&"/"&MID(A1,SEARCH(",",A1) +2,4)

if the month varies in length then this should work

=MID(A1,5,SEARCH(",",A1)-5)&"/"&LEFT(A1,SEARCH(" ", A1) -1)&"/"&MID(A1,SEARCH(",",A1) +2,4)

and then use the DATEVALUE function already suggested.

 

[softtop]

Okay, it isn't pretty but this will definitely work:

1. Insert 5 new columns just to the right of your date column (I'm assuming date is in column A.)
2. Select the whole date column (click on grey cell at the top with the letter A in it).
3. Click on Data, Text to Columns. This will bring up a box, which you select Delimited (not Fixed width), then hit Next.
4. In the next box make sure Comma and Space both have ticks next to them, and nothing else. Hit Finish.
5. This will split it out over 6 columns (your original one, plus the 5 new ones).
6. In the column where you want the real date (e.g. the one containing GMT) enter the string =DATEVALUE(B1&" "&A1&" "&C1). NB I am assuming the first date is in column A. If not, you will need to change these to be the Day, Month and Year columns respectively.
7. Copy this down the entire column (grab bottom RH corner of the cell and drag it downwards with your mouse).
8. Highlight this new column, then select Format, Cells, and pick Date and your desired format.
9. Delete/clean up the rest of the rows as needed and you should be away.

Let me know if it doesn't make sense.

Best of luck,

Henry

 

[SimonB]

Assuming dates are always in either of these two forms

MMM D, YYYY or MMM DD, YYYY

then this should work

=MID(A1,5,SEARCH(",",A1)-5)&"/"&LEFT(A1,3)&"/"&MID(A1,SEARCH(",",A1) +2,4)

if the month varies in length then this should work

=MID(A1,5,SEARCH(",",A1)-5)&"/"&LEFT(A1,SEARCH(" ", A1) -1)&"/"&MID(A1,SEARCH(",",A1) +2,4)

and then use the DATEVALUE function already suggested.

That's rubbish.... this one should work

=MID(A1,SEARCH(" ",A1),SEARCH(",",A1)-SEARCH(" ",A1)-1)&"/"&LEFT(A1,SEARCH(" ",A1)-1)&"/"&MID(A1,SEARCH(",",A1)+2,4)

and allow for any length month and day, but assumes a 4 char year

 

[RogerS]

Steve, what is it that you are trying to achieve at the end of the day? Maybe there is a simpler way?

 

[matt]

LOL - it's like a race to give you an answer. Meanwhile you've wandered off for a cuppa :lol:

 

[Steve Maskery]

Yes wandered off getting jabbed in the arm and now I'm back (even I have a bit of a life outside the internet, you know)

Roger
I have a list of all my sales and I want to plot them along a time line. Hence the date is important. I don't have format control out of Google Checkout, but I think that the ideas above will sort me out (but that doesn't mean I won't be back asking more damnfool questions before you can say Microsoft).

S

 

[RogerS]

Nah...I gave him a crossword to do :wink:

 

[RogerS]

Simon..I tried your elegant formula but iI don't think it's quite there.

Putting in Jan 1 etc in A1 drops the day in your result

Putting in Jan 16 gives 1/Jan/2010. So it's that pesky Jan 1 or Jan 10 that's messing up the dtring calcs. The comma after the month/day is always there so I was wondering if we could use that to separate out the day/month from the string.

 

[SimonB]

Hi Roger, good spot. I am using the comma to pretty much drive all string extraction, I'd missed a +1 though.

Hopefully this should do it

=MID(A1,SEARCH(" ",A1)+1,SEARCH(",",A1)-SEARCH(" ",A1)-1)&"/"&LEFT(A1,SEARCH(" ",A1)-1)&"/"&MID(A1,SEARCH(",",A1)+2,4)

 

[Steve Maskery]

=DATEVALUE(MID(E2,SEARCH(" ",E2),SEARCH(",",E2)-SEARCH(" ",E2))&"-"&LEFT(E2,SEARCH(" ",E2)-1)&"-"&MID(E2,SEARCH(",",E2)+2,4))

Perfect.
Virtual pints all round, I think.
Cheers!
S

 

[Steve Maskery]

Now if you want a virtual bag of crisps as well...

So now I have a list of entries, Sale values in Col A, a real date in Col F. Good.

I now want to make 2 columns (it can be on another sheet where the first column is a date, and the next column is the total value of sales on that date. I want to sum the n values for a particular date, to combine the n entries into one entry. I know how to do this in Access but not in Excel. I can then plot these two columns against each other on a graph.

Dry roasted anyone?
S

 

[SimonB]

Do you mean a running total?

You could try this formula

=SUM($A$1:$A1)

This would go in the first cell of your running total, and A1 is the cell containing your first sales entry. Drag the formula (black cross after hovering over black square in bottom right corner) down to the end of your date list.

 

[Steve Maskery]

Hmm, no I don't think it's a running total I need.

In English:

For each individual Date Value in Col F (Sale Date), Sum the values in Col A (Sales) where the Col A's corresponding Date Value (Sale Date) equals the Date Value (Sale Date) under consideration.

So I end up with 2 columns, Date and Total Sales on that date. I could also do the same thing with months. I can then plot Sales against Dates

Clear as murky mud viewed through dark glasses at midnight?
S

 

[matt]

pivotTable...

 

[RogerS]

vlookup?

hlookup?