Excel help - ideally before I resort to Hulk Smash and murder...

[The Psychedelic Socialist]

I usually just add a hidden cell containing =TODAY() (this will return todays date), lets say this was cell A1
Then formula will be =$A$1-B1, for the first date in the column (say all dates are in the B column)

Why wouldn't you just do it in one cell of "=TODAY()-A1" rather than having another column?

 

[Gally]

I've never felt more unintelligent than I did when I opened this thread. Absoloute mind boGgling s**t, my partner is an excel genuis and I have zero clue, ZERO!

Well done anyone that can decipher that code!

 

[Rob G]

Why wouldn't you just do it in one cell of "=TODAY()-A1" rather than having another column?

I was today year old.....

 

[Darren S]

I've never felt more unintelligent than I did when I opened this thread. Absoloute mind boGgling s**t, my partner is an excel genuis and I have zero clue, ZERO!

Well done anyone that can decipher that code!

I think the issue I have with it, is that I look at Excel being a genius and that it should just know that I want the number of days between Date A and Date B. No convoluted methods or arcane calculations - just a simple number.

Then it gets to be an arse and tells me the result is 08/03/1900.

Seriously Excel. WTF are you doing? I swear it's more stupid than ever compared to when I used Excel 2003 and similar, quite often.

 

[Darren S]

Where's Clippy when you need him?!

So - just created a new sheet to test if the formatting of the cells was important.

Formatted column A to be plain Text. Formatted Column C to Date format. As expected, cell A4 doesn't even attempt retrieve an answer and just sits there like a spanner. Cell C4 on the other hand appears to consult some mystic Precambrian calendar, removing 6 days, 6 months and 121 years for the displayed figure.

I assume that my acolyte levels of wizardry will now guide me to outputting some relevant number? I'm waaaay overthinking this - but Excel itself does not help in the slightest.

 

[The Psychedelic Socialist]

Where's Clippy when you need him?!

So - just created a new sheet to test if the formatting of the cells was important.

View attachment 1559850

Formatted column A to be plain Text. Formatted Column C to Date format. As expected, cell A4 doesn't even attempt retrieve an answer and just sits there like a spanner. Cell C4 on the other hand appears to consult some mystic Precambrian calendar, removing 6 days, 6 months and 121 years for the displayed figure.

I assume that my acolyte levels of wizardry will now guide me to outputting some relevant number? I'm waaaay overthinking this - but Excel itself does not help in the slightest.

Don't format stuff to 'Text', you almost never want that. Just leave it as general and then the formula will work.

If you format C4 as general too, it'll show correctly.

 

[Oggy997]

Don't format stuff to 'Text', you almost never want that. Just leave it as general and then the formula will work.

If you format C4 as general too, it'll show correctly.

He may need to then refresh the comments of the cell.

Also, it looks like this data is being queried, so the data type may already be set (in his previous examples)

 

[SC03OTT]

I don’t mean to be “that guy” but his name was Clippit. Not Clippy.

 

[Darren S]

I don’t mean to be “that guy” but his name was Clippit. Not Clippy.

Oh, that a low bar to set on a Wednesday.

 

[Darren S]

Don't format stuff to 'Text', you almost never want that. Just leave it as general and then the formula will work.

If you format C4 as general too, it'll show correctly.

It does indeed - thanks for that.

I told you I was overthinking it.

 

[MarkCup]

Yeah, formats, always double check your formats.

Get them wrong, don't realise you've got some set to text in error, and they can f*** you up real bad. I've spent many a half day banging my head against a brick wall because of format issues.

 

[Darren S]

Yeah, formats, always double check your formats.

Get them wrong, don't realise you've got some set to text in error, and they can f*** you up real bad. I've spent many a half day banging my head against a brick wall because of format issues.

Must have an issue with the import of data. If I use the today() formula on a clean sheet with General formatting - all is good.

When I try to use it on the sheet that I want to, I get the bloody year 1900 date.

So annoying.

 

[Oggy997]

Also, it looks like this data is being queried, so the data type may already be set (in his previous examples)

That's come as a shock 😂

Can this sheet (or is it already) one that can use macros?

 

[Darren S]

That's come as a shock 😂

Can this sheet (or is it already) one that can use macros?

It will be OK to use macros if I need to. But I'm wondering if the data I've imported to Excel is being a c**k as its a HTML file and not CSV.

Might retry that....

 

[Darren S]

A simple count help, this time around - if possible please?

I might have already gone a step too far and got myself into a dead-end - but here goes.

I've got an export of circa 32,000 rows of sales orders that had at least one order line of our top selling product within it. I now want to quantify how often other product codes appear within this list of 32k rows - giving me a list of what's numerically popular alongside the top selling product.

When I've done a simple countif formula, the results have worked (kinda) - but not summed themselves up.

So, as seen below....

18OSB	3​
18OSB	2​
18OSB	1​
24/12/3BG	2​
24/12/3BG	1​
24/12/3FW	3​
24/12/3FW	2​
24/12/3FW	1​
50MK	121​
50MK	120​
50MK	119​
50MK	118​
50MK	117​
50MK	116​
50MK	115​
50MK	114​
50MK	113​

.... the product code is on the left, followed by the counted quantity on the right.

I'd love it to appear similar to this:-

180SB 3
24/12/3BG 2
24/12/3FW 3
50MK 121

....and so on.

I've seen plenty of examples where adding the name of the product into the formula works a treat - so within in it, somewhere it would state "50MK". The problem comes with the spreadsheet being 32,000 rows tall and it would take me an age to identify each and every product code in that column.

It could be that my brain is now fried come Friday afternoon and I'm just numb to Excel again

 

[yourcodenameisnick]

Use a pivot table.

 

[Robbie Corbett]