Populate cell ranges based on a drop down

[stltom554]

Hi everyone. I have a bi weekly time sheet I am trying to automate a bit to save time for officers for payroll (and to get away from the dreaded hand written forms).

I have everything worked out as far as figuring the time in my columns but I am having some problems one two issues I can't find a solution for.

1. I am trying to populate the Schedule code, time in, and time out based on the squad. This very rarely changes so I want to be able to select, populate and change if needed. I have drop downs to keep everything uniform in the Schedule Code column, however, I don't think I can validate against the list and use a formula (like VLOOKUP) to do what I am trying to do.
2. I would like to just be able to put in the military time at 1800 and it automatically convert to 18:00. (1947=19:47 and so on).

Any help would be greatly appreciated.

 

[Yongle]

I would like to just be able to put in the military time at 1800 and it automatically convert to 18:00. (1947=19:47 and so on).

right click on range \ Format Cells \ NumberFormat \ Custom Format with this string
00":"00

 

[Yongle]

I am trying to populate the Schedule code, time in, and time out based on the squad. This very rarely changes so I want to be able to select, populate and change if needed. I have drop downs to keep everything uniform in the Schedule Code column, however, I don't think I can validate against the list and use a formula (like VLOOKUP) to do what I am trying to do.

You are correct it is one or the other

You could use VBA to populate the cells based on a lookup of standard values and then have Data Validation control any changes

 

[stltom554]

right click on range \ Format Cells \ NumberFormat \ Custom Format with this string
00":"00

View attachment 6586

Works great except now it jacks up my formula to determine the time worked. Example: =((F5-C5+(F5<C5))-(E5-D5+(E5<D5)))

I then take the time worked and convert it into 1/4 hour format: =IF(A5="g",0,IF(A5="h",0,IF(A5="V",0,IF(A5="S",0,IF(A5="CT",0,(INT(G5)*24+HOUR(G5)+ROUND(MINUTE(G5)/60,2)))))))