How to consolidate 4 separate spreasheets into one?

see attached workbook. Cell C4 has now a list of data validation to choose from (Pacific, Mountain, Central, Eastern).
The operator select the start time check in zone and see below the Premium pay hour of thenew time zone.
Hopes this helps

I see there is a problem because cell C4 is merged with cell D4. First, you have to unmerge those cells then you'll select cell C4 and then you'll be able to change the Data Validation list by going through the Data menu then Data Validation. You'll see the 4 possible choices in the SOURCE section of the Data Validation window.
I merged those 2 cells because the font was too large to fit the Mountain word. You can reduce the font size to fit cell C4 only (18 will fit)
Sorry about that.

How many "Time zone of city landing in"? Can you list them in a worksheet?
What are the flight time for all those destinations? Also listed in the same worksheet.
Anything else we need to build this, please list it in the worksheet.

Hi,
Look at the attached workbook.
I added 2 formulas in cells L2 and N2. Those values are used in the the formula in P3 to calculate the duty day.

I left the 2 cells L2 and N2 visible so you know they are there and so you could look at them but you can hide their results by changing the font color. It could be a good thing to protect those cells as well if you are protecting this sheet.

Hopes this is what you were looking for.

canada test DD PP 2.xlsxcanada test DD PP 2.xlsx

Thanks p24leclerc,

I think your spreadsheet will work for me. One question though.

I just realized there is a 5th time zone that I have to add to the drop down list and all the formulas. Can you do it? The time zone is the one just to the East of the Eastern time zone called the "AST" or Atlantic Standard time. It is of course one hour earlier than EST.

I tried to do it but I can't quite figure out add that extra "IF' function or formula...but I do know they work.
Please advise. I attached a new version of the worksheet with the new time zone added to both drop down menus (check in and block in) I just couldn't figure out the rest.
Thanks for all your help.

Sorry for the delay, I did not see your reply.
Here is the workbook modified.

You have 2 functions that does something similar to what you want:

NOW() gives you the actual date and time stamp
TODAY() gives you the actual date stamp only.

As you must enter this function in the cell ( as in "=NOW()") then you are not allowed to change only the hour. You would have to copy the cells content to itself copying only its value the modify the time. Not sure it is worth the trouble.

You could find ADDINS that would help you insert a date and time stamp in a cell. You'll have to search the web for such a thing.

Here is a work around the date issue. Can we assume that the operator could say that its "Check in" is ALWAYS Today but that its "Block in" could either be Today or Tomorrow? If so, that attached modified workbook could work for you. The operator only has to specify information in the WHITE cells. Dates have no matter here.
You have this new data validation cell asking when is the block in happening (Today or Tomorrow). There is a function in cell B5 and D5 that take this into account to calculate duty time.

About the protection now. If you select a cell then you right click your mouse, you'll see a Format cells window appears. In that window, click on the Protection tab. You can see the mark on the left side of Locked. This means that if that sheet was in a Protection mode, this cell would be protected (No change allowed, no deletion allowed, etc). By default, all the cells are Locked. So all you have to do is to unlock those WHITE cells you want the operator to be able to write on. Then you click OK and close that Format cells window.

Now you go in the REVIEW menu and select Protect Sheet. You'll be asked for a password twice. You put a password if you want but you don't have to. It will accept an empty password. The sheet is still protected but anyone can unprotect the sheet and play around. It's up to you.

Hopes this is clear enough for you. If not, call again.