Year & week number

[cul]

Hi, I'd like to display inventory on my website by the week number that it came into stock. So I made a column in my spreadsheet that reads 2020.01 2020.09 2020.10 2020.15 etc etc

When these display on the website they're having difficulty with the 2020.10 type numbers (the trailing zero I suppose). Initially calc wouldn't display them either I had to add and then remove the thousand separator (I'm not sure why that worked?), but when the website takes that data it's just displaying 2020.1 or 2019.5 etc

Is there a clean and efficient way to fix these? Either changing the data in calc to something more "readable" or perhaps using a proper date format that would actually display "2020 Week 10" or something similar.

All help appreciated!

[robleyd]

There is a function WEEKNUM that would probably help you achieve your goal. If you need more specific advice, it would help if you can provide more detail on the process involved from Calc to website.

[Villeroy]

The number 2020.10 is just number two-thousand-twenty point one. This is no date from where you can derive a year or week number.
You can format that number to show 2 decimals and then use INT(A1) to get the year and MOD(A1;1)*100 to get the week number.
I would enter year numbers and week numbers in two separate cells instead of merging them in one decimal number where the decimal fraction represents a month.

[cul]

Thank you. I'm updating the category section of my website with the year/week numbers using the data from my stock keeping spreadsheet using the import csv feature on woocommerce. (I've only been noting the purchase week since late 2019).

It currently displays as: https://furtherrecords.com/product-category/2020-16/
A failed link for 2020 week 10 looks like this: https://furtherrecords.com/product-category/2020-10/ (here you can see that 2020-1 is the link you need to use to get to week 10)


edit> I've realised I can manually edit the name and URL of the category so this is easy to fix that way, but it would mean having to manually edit every 0 ended week number every time I update my stocklist on the site.

[cul]

The number 2020.10 is just number two-thousand-twenty point one. This is no date from where you can derive a year or week number.
You can format that number to show 2 decimals and then use INT(A1) to get the year and MOD(A1;1)*100 to get the week number.
I would enter year numbers and week numbers in two separate cells instead of merging them in one decimal number where the decimal fraction represents a month.

Unfortunately the data is stored on a 3rd party website with only one field (that's actually supposed to be for location but I use it for the date) so the csv I download from that site only has a single column for dates. Obviously I could split it again for the purpose of uploading to my own site, but that seem convoluted.

Writing it in a format that calc/the website will understand as a date seems like a much better idea. Is there a format for year and week number? Or would I have to use the specific date (again which format?) and let weeknum handle it from there?

[robleyd]

Have you checked that the trailing zero is actually in the csv file you are uploading? If it isn't in your spreadsheet, it won't be exported.

It may be that when you import the data from the "third party" website, the field you are wanting to use might need to be defined as text to retain trailing zeros.

Without seeing the data you are working with, and how you are working with it, we can only guess.

[cul]

Have you checked that the trailing zero is actually in the csv file you are uploading? If it isn't in your spreadsheet, it won't be exported.

It certainly appears to be. Visually at least. But when I use cmd+h to search for 2019.50 it returns nothing. I use 2019.5 to find the examples of 2019.50. copy and paste that back into find and replace and it still finds nothing. So it definitely isn't seeing it as 2019.50 internally.

This is the file I've been using to import into the website: https://easyupload.io/nz64pt

I just tried reformatting that column as text but it didn't appear to change anything.

edit> oh and it if this wasn't laden with enough places to mess this up I also have to upload it to the 3rd party website using a csv which again appears to show 2019.50 (and works for the upload to the 3rd party site) and appears to work coming back down again...but not once I upload to my own site.

edit 2> right. in the column it displays as 2019.50 but in the top bar where I can edit it it displays as 2019.5

[Villeroy]

Why don't you mention that you import plain text? A csv file is not a spreadsheet by any means. Select the column in the import preview and import as text. The column will be imported as literal text without numeric value.

[cul]

Erm...I understand I'm not making this easy for myself or you...but I have to match up these entries with a matching ID from the site, so what I'm actually importing into the csv file "category" column that gets uploaded to the site is...

=IFERROR((VLOOKUP($B6829, $I$2:$M$7286 , 5,0)+0)," ")

I then copy that result and cmd+shift+v paste it into the upload spreadsheet to just get the raw data and no formula. The code is supposed to remove the instances from a long time ago when I did use that column for location data and I don't want it to appear as a category, so this returns either a 2019.xx or 2020.xx result or a single 0. I then cmd-f the zeros away.

Yes this is dumb but I've only just started doing it this week so I havnt made a spreadsheet that will do it all in one go (using I assume multiple sheets?)

[Villeroy]

=IFNA((VLOOKUP($B6829, $I$2:$M$7286 , 5,0)),"") works fine if $B6829 is text and column I2:M7286 is text.

Everything would be much easier if you did database jobs with a database program.

[cul]

So you can all laugh at my horrific programming skills here are the multiple sheets of data I'm using for this process. Please feel free to laugh and point out how much more streamlined it could be...

Downloaded from 3rd party site. The raw inventory data:
https://easyupload.io/wsm76g

Code to add quantity to an extra column T (which we all laughed at last week)
=IFERROR(MID(O11873, LEN("[q= "), FIND("]",O11873)-LEN("[q= ")),1)+0

This is the export from my own site. Columns A & B. Columns C onwards are my attempts to change the data using columns I copied and pasted form the raw data in the first file:
https://easyupload.io/vowh9k

This is what I then import back to my own site:
https://easyupload.io/nz64pt

Yes I'm terrible at this but I have no real training beyond reading google and asking Qs on here!

[Villeroy]

I have no idea what you are after. You lost me.
If you want to calculate the week numbers with the correct year numbers and concatenate them with a separating point:
=YEAR(A2)-AND(MONTH(A2)=1;WEEKNUM(A2)>51)+AND(MONTH(A2)=12;WEEKNUM(A2)=1)&"."&TEXT(WEEKNUM(A2);"00")
returns the year and 2-digit weeknumber separated by a point as a text value (no decimal value).
The formula subtracts one year if the weeknumber in January is greater than 51 and it adds one year if the weeknumber in December is 1.
2019-12-30 => 2020.01
Notice that WEEKNUM knows different modes to calculate the week number. The above formula uses mode 1 where the week starts with Sunday and the first week is the week having the 1st of January.