What you're really trying to average is the TIMEVALUES, so we simply add that to each of the ranges in the formulas from above:Ī2: =ARRAYFORMULA(AVERAGE(if(TIMEVALUE(A3:A7)>0.5,TIMEVALUE(A3:A7),TIMEVALUE(A3:A7)+1)))ī2: =ARRAYFORMULA(AVERAGE(TIMEVALUE(B3:B7)))Ĭ2 doesn't need to change, since it's still just dealing with durations and not dates. If you have an even number of days worth of data, it's going to give you the opposite AM/PM time value.If you miss any one day of data, your average is completely thrown off.It's calculating the time on a specific day that is the average of all the date and time values. The 0.5 assumes you don't go to bed before noon, but if you have an unusual schedule you could obviously change it.įor the average of the wake times, the above isn't probably necessary for most people because generally you wake up between midnight one day and midnight the next, so none of your values need to "wrap around." That said:Īdding dates really only masks the issues with calculating averages, because it's using both the date and the time for the average. The above formula simply takes each value in the range, checks if it's over 0.5 (noon), and if it's not adds 1 to move it to the next day, then averages all the values. The best time to sleep and wake up will vary among individuals, but most adults should aim to go to bed between 8 p.m. Waking up around those times maximizes the likelihood of waking during the increased awareness period at the end of the cycle, right after the end of the REM phase. With personalized treatment for whatever is keeping you up at night, including CPAP prescription for sleep apnea, you (and your. there might be some values on one side and some on the other). To calculate them, we simply incrementally added 90 minutes to the starting time of your sleep. Once you have the durations, the average is simply:įor the averages of the actual times you'll need to use ARRAYFORMULA if there's the possibility that the times in any one column are going to need to "wrap around" midnight (i.e. Hopefully you get some excellent rest and you enjoy the tool! Good night.To do this accurately without having to add dates:Īs mentioned in a comment, to get the "Hours Slept" to work, you'll need to use MOD since time "wraps around" at 12 PM/24:00, which is equal to 1 in the spreadsheet world. There are three ways to use this calculator to generate your custom bedtime duration. Based on how long you normally sleep and the times you need to wake up or will be getting to bed, this sleep duration calculator can help optimize your rest. Use this calculator to compute what time to go to bed or get up to wake up refreshed between sleep cycles. The calculator is designed to help you wake up during Stage 1, the lightest rest. However, it's often difficult to properly plan your sleep when you have things to do one evening, or early plans. We all know how important it is to sleep – there's got to be a reason we spend between 1/4 and 1/3 of our lives doing it! Is Midnight AM or PM? How About Noon?ġ2:00 AM is midnight and 12:00 PM is noon. The field name will change depending on you inputs, and the field will tell you how to plan your sleep. Tired of sleepless nights Not sure what time to wake up Try Paperforms sleep calculator to improve your sleep based on your natural circadian rhythm. Depending on your input, we'll tell you either when to wake up or set an alarm, or instead when to go to bed. Next, click the blue 'Calculate Sleep' button. Do your best to avoid getting fewer than 4 cycles, or 6 hours of sleep. Base your sleep cycle start time on the time you need to be up. It also allows 15 minutes to sink into sleep and 15 minutes to wake up.
0 Comments
Leave a Reply. |