Search code examples
excelvbadecimalrecurring

Excel custom number format for recurring decimal

I'm trying to make an auto roster spreadsheet with 5-3 and 6-2 weeks rotation, I have successfully used the 1.33 factor in the code below for the 6-2 weeks rotation.

   If OnSite = (Days * Format(1.33, "#,##0.0033333333333333")) + 1 Then
    OnSite = 1
   End If

But I can't get the desired result for 1.66 factor when using similar custom number format in 5-3 weeks rotation.

If OnSite = (Days * Format(1.66, "#,##0.0066666666666667")) + 1 Then
    OnSite = 1
   End If

Edit: Days = 35, I want to get a whole number only from Days x 1.66 to make the Onsite = 1

Solution

  • I have overlooked and focused to 1.66 factor but then I realized that I can get a whole number only from Days (35) x 1.6 = 56. Code below works perfectly now.

    If OnSite = (Days * 1.6) + 1 Then
    OnSite = 1
   End If
``