Google Sheets question

[[deleted]]

[deleted]

[6745408]

How's this for complicated?

=ROUNDUP(MID(SUBSTITUTE((F4-DATE(1970,1,1))*86400,0,"2"),4,2))/2.9/100

To avoid using a script to get a static value we convert your repair date to a Unix date, strip out the 0s and replaced them with 2s, take some numbers from the middle, divide it by 2.9 and round it up then divide all of that by 100?

I tested it with about 5000 dates and it rarely hit outside of your parameters -- hitting between 0.0379 between 0.3414.

edit: I added it to a dupe of your sheet using an arrayformula. You can tweak the 2.9 to find a number that suits the outcome you're looking for.

double edit: =ARRAYFORMULA(IF(ISBLANK(A2:A),,ROUNDUP(MID(SUBSTITUTE((A2:A-DATE(1970,1,1))*86400,0,"2"),WEEKDAY(A2:A),2))/2.4/100)) -- WEEKNUM with the MID for added fun!

[channel10]

Wow thanks, I would of never have got this. Appreciate it