Is there any way to use a custom number scale on Excel?

[jonybandana]

Hello! I am trying to build a celendar using [Year-week number] as a numbering scale to try to build a Gantt diagram. My idea is to have something like this:

Project	Act1	Act1 duration	Act2
test project 1	2351​	10​	2361​
test project 2	2348​	10​	2358​

My idea is to basically have the numbering end on 2352 (Since there are only 52 weeks in the year) and then start on 2401, so that I can then make the Gantt chart based on that info using conditional formatting.

Can I do this somehow?

Thank you!

 

[6StringJazzer]

It is much better to use actual dates, because then you can do normal date arithmetic with them. Then you can display the dates in the format you want:

=MOD(YEAR(A1),100) & WEEKNUM(A1)

To come up with your own custom date method would require backflips to do simple things like subtract one date from another to determine duration, or add a duration to a date.

Also I note your example shows dates of 2361 and 2358, which are week numbers >52. Not sure what you have in mind there.

And last, a year has 52 weeks + 1 day (+ 2 days in leap year). How will you account for the extra day(s)?

 

[ExceLoki]

agree with 6string
but if you do want to continue the way you currently have it, then try one of these in E or F
-------------------

Book1
	A	B	C	D	E	F
5	Project	Act1	Act1 duration	Act2	v1	v2
6	test project 1	2351	10		2410	2410
7	test project 2	2348	10		2407	247
Sheet1

Cell Formulas
Range	Formula
E6:E7	E6	=LET(SOY,DATE(20&VALUE(LEFT(B6,2)),1,1),WKN,((VALUE(RIGHT(B6,2))+C6)*7),VALUE(RIGHT(YEAR(WKN+SOY),2)&TEXT(WEEKNUM(WKN+SOY,1),"00")))
F6:F7	F6	=LET(act,((VALUE(RIGHT(B6,2))+C6)*7)+DATE(20&VALUE(LEFT(B6,2)),1,1),MOD(YEAR(act),100) & WEEKNUM(act))