Working out calendar days from week number and day (42.1)

Question

struggling to find anything on here regarding my query, which leads me to believe it may be impossible but here goes!

my company creates products that take takes around two weeks to fully build from start to finish. The whole company works in weeks and days, for example we would start building on week 14.1 (14th week and on Monday) and finish building on week 16.5 (16th week and on Friday). The company is massive so I cannot convert them to use dates instead.

We want to capture data from a spreadsheet that has 5 columns, A = build week , B = build day, C = Completion week, D = Completion day, E = calendar days.

At present we just manually work out the amount of calendar days as it is difficult to write a formula to differentiate between knowing week 52.5 and week 1.1 is only 4 days.

is it possible to write a formula or use a VBA code to work out the amount of calendar days between two week numbers from the data I have or am I testing the limits of excel?

any help would be great.

Answer 1

Use:

=((C2+(C2<A2)*52)*7+D2)-(A2*7+B2)+1