February 15, 2021
Hi,
Could a expert help me with this problem.
I am trying to highlight the Range of rows based on 24 hour.
EG: if I select day to be: Tue with 36 hrs and start time 12:25
i want finish time to be calculated:??
and mainly highlight row from 12:25 tue plus wed to cover 36 hrs. but if i add additional 2 hrs then thur must be highlighted till 2am
please see the attachment.
Thank you all experts.
VIP
Trusted Members
December 7, 2016
VIP
Trusted Members
December 7, 2016
Hello Andy,
Thanks for the file. Regarding calculating the end time you should be able to crack that nut if you read the good information about date and time in this site, you can for example start by exploring the resources section to see what it has to reveal.
Regarding highlighting cells I would say you need to structure your range of cells better than you have currently. For example you have two columns with no headers and the columns having headers, those are text but the time value you want to check against is number. To make things easier they should be in same format. If no one else get there before me with an example I will look into it tomorrow Wednesday.
Just wondering about the time accuracy, your start time is 12:25 and adding 36 hours your end time is then 00:25, we are then in Thursday, so shouldn’t the first cell on Thursday row be highlighted also? Seems you have some sort of time margin that is not stated.
Br,
Anders
VIP
Trusted Members
December 7, 2016
Hello Andy,
Attached is a simple sample file, it is not a final solution, but it gives perhaps a base to build on. With the given layout it gets more complex as you need to instruct Excel what is valid or not. If want some part of the cell to be highlighted then you would need another approach and most likely a more advanced layout.
Br,
Anders
February 15, 2021
Hi Anders
Thank you so much for good Start. Yes! You're correct again. I will need more advanced format. Would you be able to help me with this please!
I also noticed two things aren't working.
1: If Start Day is Wed
Start time: 13:00
Hours: 36
End day is calculated to be Fri. However the Row for Thur not highlighting and this is happening for all other days too if I go above 35hrs. The day (ROW) in between not highlighting.
2: Columns header (Time) are increment of 6 hours would it be possible to have it via 15min increment?
Once again thank you ever so much for your Expert support. You're a Star!
VIP
Trusted Members
December 7, 2016
Hello Andy,
Very kind of you, even though I am not a star I gladly try to help.
I’m sorry, I don’t intend to help you with a complete file or solution. And yes, I am aware of the flaws in the sample file I provided, it is after all a simple example file. Take the opportunity to read the articles you find here and learn how to work with dates and times. Hopefully you then will find out how to best structure the table or range to suite your needs. Perhaps some of the steps I took in the sample file can give some guidance, it is what I try to achieve, to help others to be more efficient with Excel.
As you probably already know, even if simple in thought, it is a complex task you want to do.
Don’t hesitate to ask for help, but I hope it is more of ”how do I do” than ”please do the work for me”.
Best of luck and stay safe!
/Anders
Answers Post
1 Guest(s)