Consulting

Results 1 to 5 of 5

Thread: formula help

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location

    formula help

    I need to change this to generate random times from 0:00 to 5:00 in minutes.

    =TEXT(RAND()*(9-6)/24+6/24,"MM:SS")
    Can you use the BETWEEN in this?
    Peace of mind is found in some of the strangest places.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    =RAND()/288
    formatted as time or
    =TEXT(RAND()/288,"MM:SS")

    If that 0:00 to 5:00 is midnight to 5am then
    =MROUND(RAND()*5/24,1/1440)
    formatted as you will.
    Last edited by p45cal; 02-28-2018 at 04:58 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Thanks. what does the /288 do?
    Peace of mind is found in some of the strangest places.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    288 is 24*12
    24 hours in the day, 12 is the number of 5-minutes in an hour.
    So 288 5-minute periods in a day.
    Because RAND() returns a value from 0 to 1, divide that max (1) by 288 returns 5 minutes (the max). Any lower value returned by RAND() returns any time from 0 to 5 minutes.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    thanks
    Peace of mind is found in some of the strangest places.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •