First off, my project is a weekly sleep log I am creating. Cell A2 contains the Start Date of the log. Range C4-P4 contain the time one went to bed. Range C5-P5 contain the time when one woke up. Each of these ranges are double cell merged, which is why there are so many cells for a weekly log, and both of the time ranges are formatted as time.
What I am looking for here is this;
I've done a lot of searching trying to figure this out on my own, and after 6 days, I think it is time to ask for help. So, with that in mind, any and all help in this would be very much appreciated.
What I am looking for here is this;
- My date field needs to auto-update every week. It should only replace itself, if it has been a week or more since the currently inputted date.
- I also need to figure out how to calculate the amount of time one is awake for. So far, I am using the below code. This does indeed work, provided there is a time entered within E4; however, if someone did not go to sleep for an entire day, then the calculation is messed up because we have empty cells. I am including a screenshot to help with the explanation.
Code:
=IFERROR(TEXT(IF(C5>E4,E4+1-C5,E4-C5),"h") & " hours and " & MINUTE(IF(C5>E4,E4+1-C5,E4-C5)) & " minutes.", "0 hours and 0 minutes.")
I've done a lot of searching trying to figure this out on my own, and after 6 days, I think it is time to ask for help. So, with that in mind, any and all help in this would be very much appreciated.
