Simplify data entry for rota

[L0s3r]

Dear people out there,

i need you help to write a rota for my employee.

i will try it to explain as good as i can.

we have four different time of shift:

06:30 to 15:00
09:00 to 17:30
14:30 to 23:00
22:30 to 07:00

what i would like to do is that when i have to input the time instead of thyping "06:30" i could only tipe "1" and the system will understand that "1" means "06:30", "2" would mean "09:00" and so on.

How could i do that?

[Pete_UK]

You could use a multiple-IF or a VLOOKUP formula, in conjunction with a small table. Attach a sample Excel workbook, as explained in the yellow banner at the top of the screen.

Pete

[AliGW]

Welcome to the forum.

Which latest version of Excel do you have - Excel 2019 or MS365? Please update your forum profile accordingly. Thanks.

There are instructions at the top of the page explaining how to attach your sample workbook.

If you want Excel to REPLACE these numbers with times, then you will need VBA. If you want the times to appear in an adjacent cell, then Pete's suggestion will work.

[6StringJazzer]

There is more than one way to do this. It depends on how you are going to use the shift times in the rest of your spreadsheet. Can you attach a sample file showing how you will record and use this data?

First I would set up a lookup table with the shift times.

One option is to enter 1 and everywhere that time is needed, interpret that as Shift 1 (06:30 - 15:00). Another is to have a column where you enter 1, then in the next column do a lookup to show 06:30 and in the next column do another lookup to show 15:00.

In either case you could use data validation with a dropdown list to ensure that the user enters a valid number.

Example:

Values as displayed

	A	B	C	D	E	F	G
1	Lookup Table				Data Entry
2	Shift	Start	End		Shift	Start	End
3	1	6:30	15:00		2	9:00	17:30
4	2	9:00	17:30
5	3	14:30	23:00
6	4	22:30	7:00

Underlying formulas

	A	B	C	D	E	F	G
1	Lookup Table				Data Entry
2	Shift	Start	End		Shift	Start	End
3	1	0.270833333333333	0.625		2	=INDEX(B$3:B$6,$E3)	=INDEX(C$3:C$6,$E3)
4	2	0.375	0.729166666666667
5	3	0.604166666666667	0.958333333333333
6	4	0.9375	0.291666666666667