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

jonybandana

New Member
Joined
Dec 16, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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:

ProjectAct1Act1 durationAct2
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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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:

Excel Formula:
=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)?
 
Upvote 0
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
ABCDEF
5ProjectAct1Act1 durationAct2v1v2
6test project 123511024102410
7test project 22348102407247
Sheet1
Cell Formulas
RangeFormula
E6:E7E6=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:F7F6=LET(act,((VALUE(RIGHT(B6,2))+C6)*7)+DATE(20&VALUE(LEFT(B6,2)),1,1),MOD(YEAR(act),100) & WEEKNUM(act))
 
Upvote 1

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top