date and time issues!
in this spreadsheet i have people checking in with me. i have set the dates and times up so the boxes shade automatically, but the problem is it only works for the date set in the sign in and out now, if you change the dates for peoples shifts it doesnt shade, i figure the boxes on the top need the date changed, but is there any way to do this automatically??
Check Call problem.xlsm
Please help guys, i really need this complete ASAP
Anthony
Is this thread the same question?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
A simpler question, and probably more solvable, would be:
Can i use a formula when inputting dates and times to have all the dates the same but set times i.e. =TODAY + 08:00, =TODAY + 09:00 etc..
So i could just enter todays dates and times on the left boxes for sign in and sign out and the top row would be set as todays date but the times already set at the top????
no, in the other thread its to do with shading, this one is for dates. Ive sorted the problem in the other thread.
Ill try and put this thread question easier. In the spreadsheet i attached, ive now set the CF in the boxes to shade automatically when it passes a certain time. the problem im having now is that the times on the top row, from 08:00 all the way to 07:00, need changed to fit the same day as the dates on the left in the sign in and out boxes. so if that rota is for, say, 20th march 2014, the left hand boxes would be updated by the user, all to the 20th march, but the top row is set to the same date, so when you update the dates on the left, the boxes do not highlight anymore as the formula states otherwise.
OK, in the other thread, be sure to give your helper feedback, and mark the thread as solved. Thanks.
The formulas you've suggested are almost perfect.
1) A1: Enter the date to use for this sheet. I do not recommend =TODAY() as that will change everytime you open the workbook, not good for historical sheets.
2) To show date from A1 plus 8am in another cell:
=A1+TIMEVALUE("8:00 AM")
3) Be sure to apply a number format to that second cell that will display the date AND the time... such as mm/dd/yyyy h:mm
now the formulas dont work...
Check Call problem.xlsm
solved.....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
brucey2343 
Register To Reply
Bookmarks