Numbers format help

[Geek Girl 007]

Hi all,
I have created a specific Leave Sheet, it includes cells for an 11 hour day that equals 1 day, a public holiday that equals 1 day and the opportunity for ad-hoc leave, this is what I've done, however I know I miscalculated and I know the format cells are all wrong, any advice would be appreciated.

TIMESHEET-KIM PIPER.xlsx
	D	E	F	G	H	I	J
43		LEAVE			PUBLIC HOLIDAY
44	W/C
45	05/12/2022	Hrs : Mins	Leave Days	Leave hrs	Hrs : Mins	Days	SELECT
46	Monday		0.0	2	7:24	1.0	PH
47	Tuesday	11:00	1.0
48	Wednesday	11:00	1.0
49	Thursday		0.0
50	Friday		0.0
51
52		22:00	2.0		7:24	1.0
53
54		Totals
55		Yearly Rollover AL (if appropriate)				271	41
56				Hrs : Mins		Days
57		Previous AL		271		41
58		Sub Total		3.23		3
59		Total		268		38
1 (3)

Cell Formulas
Range	Formula
H46	H46	=IF(OR(J46={"PH"}),TIME(7,24,0),"")
I46:I50	I46	=IFERROR(CEILING(H46*24/10,0.5), "")
H47	H47	=IF(OR(J47={"PH","no"}),TIME(7,24,0),"")
H48:H50	H48	=IF(OR(J48={"PH","Sick","Covid"}),TIME(7,24,0),"")
F46:F50	F46	=CEILING(E46*20/10,0.5)
E52:F52,H52:I52	E52	=SUM(E46:E50)
G57	G57	=I55
I57	I57	=J55
G58	G58	=E52+F52+H52
I58	I58	=F19+I19+F30+I30+F41+I41+F52+I52
G59,I59	G59	=G57-G58

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G57:J59	Cell Value	=0	text	NO
J46:J50	Cell Value	=0	text	NO
H52	Cell Value	=0	text	NO
H52	Expression	=#REF!="Covid"	text	NO
H52	Expression	=#REF!="Sick"	text	NO
H52	Expression	=#REF!="Toil"	text	NO
H52	Expression	=#REF!="PH"	text	NO
I52	Cell Value	=0	text	NO
I52	Expression	=#REF!="Covid"	text	NO
I52	Expression	=#REF!="Sick"	text	NO
I52	Expression	=#REF!="Toil"	text	NO
I52	Expression	=#REF!="PH"	text	NO
F46:F50	Cell Value	=0	text	NO
E52:F52	Cell Value	=0	text	NO
E52:F52	Expression	=#REF!="Covid"	text	NO
E52:F52	Expression	=#REF!="Sick"	text	NO
E52:F52	Expression	=#REF!="Toil"	text	NO
E52:F52	Expression	=#REF!="PH"	text	NO
D52	Cell Value	contains "Excess Hours"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E46:E50	List	=$N$19
J46:J50	List	=$N$10:$N$11

 

[MCLIFTO8]

What do you need need your numbers formatted to? Is it rows 57-59 that are the issue?
To convert a time in hours with decimals you can try [h]:mm;@ from Time custom format

Further reading
Custom number formats
Generic custom number format table

 

[Geek Girl 007]

Hi, I tried that and this is what happened??
I don't know if I am going to be able to make this work?

TIMESHEET.xlsx
	D	E	F	G	H	I	J	K
43		LEAVE			PUBLIC HOLIDAY
44	W/C
45	05/12/2022	Hrs : Mins	Leave Days	Leave hrs	Hrs : Mins	Days	SELECT
46	Monday		0.0	2	7:24	1.0	PH
47	Tuesday	11:00	1.0
48	Wednesday	11:00	1.0
49	Thursday		0.0
50	Friday		0.0
51
52		22:00	2.0		7:24	1.0
53
54		Totals
55		Yearly Rollover AL (if appropriate)				271	41	41
56				Hrs : Mins		Days
57		Previous AL		271		41
58		Sub Total		77:24		3
59		Total		6426:36		38
1 (3)

Cell Formulas
Range	Formula
H46	H46	=IF(OR(J46={"PH"}),TIME(7,24,0),"")
I46:I50	I46	=IFERROR(CEILING(H46*24/10,0.5), "")
H47	H47	=IF(OR(J47={"PH","no"}),TIME(7,24,0),"")
H48:H50	H48	=IF(OR(J48={"PH","Sick","Covid"}),TIME(7,24,0),"")
F46:F50	F46	=CEILING(E46*20/10,0.5)
E52:F52,H52:I52	E52	=SUM(E46:E50)
G57	G57	=I55
I57	I57	=J55
G58	G58	=E52+F52+H52
I58	I58	=F19+I19+F30+I30+F41+I41+F52+I52
G59,I59	G59	=G57-G58

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F46:F50	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E46:E50	List	=$N$19
J46:J50	List	=$N$10:$N$11

 

[MCLIFTO8]

Problem
It looks like you want column G rows 56-59 to be in hours:minutes format. Let me know if that's incorrect.

Input
For cells E46:E50 and H46:H50 enter all your hh:mm times as they're shown. eg, you would enter 7:24 as 7:24.
For all cells on row 52 leave all your formulas as they are.

For "Previous AL" Excel is taking your 271 days as being 24 hour long days, to get your AL into the excel hours format you need to convert your days into hours, then back into days worth of pure leave. If your days are typically 11 hours, then the total AL in hours would be 271*11/24 = 124.2 days of leave. == 2,980.8 hours of leave available.
therefore the formula in cell G57 might be =I55*11/24

If that 271 figure is hours of leave, simply divide it by 24. G57 = I55/24. I'm assuming this in the below screenshot.

Formatting

format cells E46:E50, E52:F52 and G57:G59 with the custom number format [h]:mm Use the format painter icon to make this easier.

You should end up with something like this:

 

[Geek Girl 007]

Problem
It looks like you want column G rows 56-59 to be in hours:minutes format. Let me know if that's incorrect.

Input
For cells E46:E50 and H46:H50 enter all your hh:mm times as they're shown. eg, you would enter 7:24 as 7:24.
For all cells on row 52 leave all your formulas as they are.

For "Previous AL" Excel is taking your 271 days as being 24 hour long days, to get your AL into the excel hours format you need to convert your days into hours, then back into days worth of pure leave. If your days are typically 11 hours, then the total AL in hours would be 271*11/24 = 124.2 days of leave. == 2,980.8 hours of leave available.
therefore the formula in cell G57 might be =I55*11/24

If that 271 figure is hours of leave, simply divide it by 24. G57 = I55/24. I'm assuming this in the below screenshot.

Formatting
View attachment 80723
format cells E46:E50, E52:F52 and G57:G59 with the custom number format [h]:mm Use the format painter icon to make this easier.

You should end up with something like this:
View attachment 80725

I've tried to make this work but I am struggling.
I've changed it again without the days only hours....


The 11 hours is formatted in number the 7.24 is [h}:mm
I don't know how to make the totals add up
Can anyone help please?

TIMESHEET-KIM PIPER.xlsx
	B	C	D	E	F	G	H	I	J	K
10				LEAVE			PUBLIC HOLIDAY
11			W/C
12			28/02/2022	Hrs : Mins	Leave hrs		Hrs : Mins	SELECT
13			Monday	11			7:24	PH
14			Tuesday	11
15			Wednesday
16			Thursday
17			Friday				7:24	PH
18								TOTAL
19				22.00	0.00		14:48	22.62
1

Cell Formulas
Range	Formula
D12	D12	=E8
H13	H13	=IF(OR(I13={"PH"}),TIME(7,24,0),"")
H14	H14	=IF(OR(I14={"PH","no"}),TIME(7,24,0),"")
H15:H17	H15	=IF(OR(I15={"PH","Sick","Covid"}),TIME(7,24,0),"")
E19:F19,H19	E19	=SUM(E13:E17)
I19	I19	=SUM(E19:H19)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I13:I17	Cell Value	=0	text	NO
H19	Cell Value	=0	text	NO
H19	Expression	=#REF!="Covid"	text	NO
H19	Expression	=#REF!="Sick"	text	NO
H19	Expression	=#REF!="Toil"	text	NO
H19	Expression	=#REF!="PH"	text	NO
E19:F19	Cell Value	=0	text	NO
E19:F19	Expression	=#REF!="Covid"	text	NO
E19:F19	Expression	=#REF!="Sick"	text	NO
E19:F19	Expression	=#REF!="Toil"	text	NO
E19:F19	Expression	=#REF!="PH"	text	NO
D19	Cell Value	contains "Excess Hours"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I13:I17	List	=$M$3:$M$4
E13:E17	List	=$L$3:$L$4

 

[MCLIFTO8]

The cells only add up in a way you expect when they are all the same format. The common format in your speadsheet is hours.
You need this [h]:mm in your custom format for the cells listed in my answer.

Highlight the cells with hours in them and press "crtl 1" then paste [h]:mm into the custom number format box.

Time formatting in Excel: 12/24 hour, custom, default

Everything you need to know about formatting time in Excel: how to change time to 12 hour or 24 hour format, apply custom formatting, format date time values, and more.

www.ablebits.com

Once you're able to convert everything to hours, it's just a case of tweaking the formulas so that the hours of leave convert to days of leave, that's done with a formula to work out how many hours of paid leave = 1 day of paid leave.

 

[Geek Girl 007]

The cells only add up in a way you expect when they are all the same format. The common format in your speadsheet is hours.
You need this [h]:mm in your custom format for the cells listed in my answer.

Highlight the cells with hours in them and press "crtl 1" then paste [h]:mm into the custom number format box.

Time formatting in Excel: 12/24 hour, custom, default

Everything you need to know about formatting time in Excel: how to change time to 12 hour or 24 hour format, apply custom formatting, format date time values, and more.

www.ablebits.com

Once you're able to convert everything to hours, it's just a case of tweaking the formulas so that the hours of leave convert to days of leave, that's done with a formula to work out how many hours of paid leave = 1 day of paid leave

Hi,
I gave up on the 'days' part of this sheet as this member of staff works different hours to a standard day, so it will be for them to work out.
So I reformatted it like this and now I have these numbers.......... I don't know how to make it work!

TIMESHEET-LEAVE.xlsx
	B	C	D	E	F	G	H	I	J	K
2
3
4
5
6			Name: .
7
8				28/02/2022	<-- Insert start date here
9
10				LEAVE			PUBLIC HOLIDAY
11			W/C
12			28/02/2022	Hrs : Mins	Leave hrs		Hrs : Mins	SELECT
13			Monday	264:00			7:24	PH
14			Tuesday	264:00
15			Wednesday
16			Thursday
17			Friday				7:24	PH
18								TOTAL
19				22.00	0.00		14:48	22.62
20
21				LEAVE			PUBLIC HOLIDAY
22			W/C
23			07/03/2022	Hrs : Mins	Leave hrs		Hrs : Mins	SELECT
24			Monday
25			Tuesday
26			Wednesday
27			Thursday
28			Friday
29
30				0:00	0:00		0:00	0:00
31
32				LEAVE			PUBLIC HOLIDAY
33			W/C
34			14/03/2022	Hrs : Mins	Leave hrs		Hrs : Mins	SELECT
35			Monday
36			Tuesday
37			Wednesday
38			Thursday
39			Friday
40
41				0:00	0:00		0:00	0:00
42
43				LEAVE			PUBLIC HOLIDAY
44			W/C
45			21/03/2022	Hrs : Mins	Leave hrs		Hrs : Mins	SELECT
46			Monday
47			Tuesday
48			Wednesday
49			Thursday
50			Friday
51
52				0:00	0:00		0:00	0:00
53
54				Totals
55				Yearly Rollover AL (if appropriate)			271		41
56							Hrs : Mins
57				Previous AL			6504:00
58				Sub Total			542:48
59				Total			5961:12
60
61
62
63
1

Cell Formulas
Range	Formula
D12	D12	=E8
H13,H46,H35,H24	H13	=IF(OR(I13={"PH"}),TIME(7,24,0),"")
H14,H47,H36,H25	H14	=IF(OR(I14={"PH","no"}),TIME(7,24,0),"")
H15:H17,H48:H50,H37:H39,H26:H28	H15	=IF(OR(I15={"PH","Sick","Covid"}),TIME(7,24,0),"")
E19:F19,E52:F52,E41:F41,E30:F30,H19	E19	=SUM(E13:E17)
I19,I52,I41,I30	I19	=SUM(E19:H19)
D23,D45,D34	D23	=D12+7
H30,H52,H41	H30	=SUM(H24:H27)
H57	H57	=H55
H58	H58	=I19+I30+I41+I52
H59	H59	=H57-H58

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I46:I50	Cell Value	=0	text	NO
I35:I39	Cell Value	=0	text	NO
I24:I28	Cell Value	=0	text	NO
H52	Cell Value	=0	text	NO
E52:F52	Cell Value	=0	text	NO
D52	Cell Value	contains "Excess Hours"	text	NO
H41	Cell Value	=0	text	NO
E41:F41	Cell Value	=0	text	NO
D41	Cell Value	contains "Excess Hours"	text	NO
H30	Cell Value	=0	text	NO
E30:F30	Cell Value	=0	text	NO
D30	Cell Value	contains "Excess Hours"	text	NO
I13:I17	Cell Value	=0	text	NO
H19	Cell Value	=0	text	NO
E19:F19	Cell Value	=0	text	NO
D19	Cell Value	contains "Excess Hours"	text	NO
H57:I59	Cell Value	=0	text	NO
E8	Cell	contains a blank value	text	NO
E8	Cell Value	contains "Type your name in here."	text	NO
E6	Cell Value	contains "Type your name in here."	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E24:E28	List	=$M$19
E46:E50	List	=$M$19
E35:E39	List	=$M$19
I13:I17	List	=$M$3:$M$4
I24:I28	List	=$M$3:$M$4
I35:I39	List	=$M$3:$M$4
I46:I50	List	=$M$3:$M$4
E13:E17	List	=$L$3:$L$4

 

[Geek Girl 007]

Hi,
I gave up on the 'days' part of this sheet as this member of staff works different hours to a standard day, so it will be for them to work out.
So I reformatted it like this and now I have these numbers.......... I don't know how to make it work!

TIMESHEET-LEAVE.xlsx
	B	C	D	E	F	G	H	I	J	K
2
3
4
5
6			Name: .
7
8				28/02/2022	<-- Insert start date here
9
10				LEAVE			PUBLIC HOLIDAY
11			W/C
12			28/02/2022	Hrs : Mins	Leave hrs		Hrs : Mins	SELECT
13			Monday	264:00			7:24	PH
14			Tuesday	264:00
15			Wednesday
16			Thursday
17			Friday				7:24	PH
18								TOTAL
19				22.00	0.00		14:48	22.62
20
21				LEAVE			PUBLIC HOLIDAY
22			W/C
23			07/03/2022	Hrs : Mins	Leave hrs		Hrs : Mins	SELECT
24			Monday
25			Tuesday
26			Wednesday
27			Thursday
28			Friday
29
30				0:00	0:00		0:00	0:00
31
32				LEAVE			PUBLIC HOLIDAY
33			W/C
34			14/03/2022	Hrs : Mins	Leave hrs		Hrs : Mins	SELECT
35			Monday
36			Tuesday
37			Wednesday
38			Thursday
39			Friday
40
41				0:00	0:00		0:00	0:00
42
43				LEAVE			PUBLIC HOLIDAY
44			W/C
45			21/03/2022	Hrs : Mins	Leave hrs		Hrs : Mins	SELECT
46			Monday
47			Tuesday
48			Wednesday
49			Thursday
50			Friday
51
52				0:00	0:00		0:00	0:00
53
54				Totals
55				Yearly Rollover AL (if appropriate)			271		41
56							Hrs : Mins
57				Previous AL			6504:00
58				Sub Total			542:48
59				Total			5961:12
60
61
62
63
1

Cell Formulas
Range	Formula
D12	D12	=E8
H13,H46,H35,H24	H13	=IF(OR(I13={"PH"}),TIME(7,24,0),"")
H14,H47,H36,H25	H14	=IF(OR(I14={"PH","no"}),TIME(7,24,0),"")
H15:H17,H48:H50,H37:H39,H26:H28	H15	=IF(OR(I15={"PH","Sick","Covid"}),TIME(7,24,0),"")
E19:F19,E52:F52,E41:F41,E30:F30,H19	E19	=SUM(E13:E17)
I19,I52,I41,I30	I19	=SUM(E19:H19)
D23,D45,D34	D23	=D12+7
H30,H52,H41	H30	=SUM(H24:H27)
H57	H57	=H55
H58	H58	=I19+I30+I41+I52
H59	H59	=H57-H58

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I46:I50	Cell Value	=0	text	NO
I35:I39	Cell Value	=0	text	NO
I24:I28	Cell Value	=0	text	NO
H52	Cell Value	=0	text	NO
E52:F52	Cell Value	=0	text	NO
D52	Cell Value	contains "Excess Hours"	text	NO
H41	Cell Value	=0	text	NO
E41:F41	Cell Value	=0	text	NO
D41	Cell Value	contains "Excess Hours"	text	NO
H30	Cell Value	=0	text	NO
E30:F30	Cell Value	=0	text	NO
D30	Cell Value	contains "Excess Hours"	text	NO
I13:I17	Cell Value	=0	text	NO
H19	Cell Value	=0	text	NO
E19:F19	Cell Value	=0	text	NO
D19	Cell Value	contains "Excess Hours"	text	NO
H57:I59	Cell Value	=0	text	NO
E8	Cell	contains a blank value	text	NO
E8	Cell Value	contains "Type your name in here."	text	NO
E6	Cell Value	contains "Type your name in here."	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E24:E28	List	=$M$19
E46:E50	List	=$M$19
E35:E39	List	=$M$19
I13:I17	List	=$M$3:$M$4
I24:I28	List	=$M$3:$M$4
I35:I39	List	=$M$3:$M$4
I46:I50	List	=$M$3:$M$4
E13:E17	List	=$L$3:$L$4

Hi I still hadn't formatted them all, this is the full sheet.
Public holiday will always be 7.24
full day will always be 11.00 and Ad Hoc will be Ad Hoc.... I don't know how to format it to give me a runnign total.
Can you help?

TIMESHEET-LEAVE.xlsx
	B	C	D	E	F	G	H	I	J	K
2
3
4
5
6			Name: .	Type your name in here.
7
8					<-- Insert start date here
9
10				LEAVE			PUBLIC HOLIDAY
11			W/C
12			00/01/1900	Full Day	Ad Hoc		Hrs : Mins	SELECT
13			Monday	264:00			7:24	PH
14			Tuesday	264:00
15			Wednesday
16			Thursday
17			Friday				7:24	PH
18								TOTAL
19				528:00	0:00		14:48	542:48
20
21				LEAVE			PUBLIC HOLIDAY
22			W/C
23			07/01/1900	Full Day	Ad Hoc		Hrs : Mins	SELECT
24			Monday
25			Tuesday
26			Wednesday
27			Thursday
28			Friday
29								TOTAL
30				0:00	0:00		0:00	0:00
31
32				LEAVE			PUBLIC HOLIDAY
33			W/C
34			14/01/1900	Full Day	Ad Hoc		Hrs : Mins	SELECT
35			Monday
36			Tuesday
37			Wednesday
38			Thursday
39			Friday
40								TOTAL
41				0:00	0:00		0:00	0:00
42
43				LEAVE			PUBLIC HOLIDAY
44			W/C
45			21/01/1900	Full Day	Ad Hoc		Hrs : Mins	SELECT
46			Monday
47			Tuesday
48			Wednesday
49			Thursday
50			Friday
51								TOTAL
52				0:00	0:00		0:00	0:00
53
54				Totals
55				Yearly Rollover AL (if appropriate)			6504:00		41
56							Hrs : Mins
57				Previous AL			6504:00
58				Sub Total			542:48
59				Total			5961:12
60
61
62
63
1

Cell Formulas
Range	Formula
D12	D12	=E8
H13,H46,H35,H24	H13	=IF(OR(I13={"PH"}),TIME(7,24,0),"")
H14,H47,H36,H25	H14	=IF(OR(I14={"PH","no"}),TIME(7,24,0),"")
H15:H17,H48:H50,H37:H39,H26:H28	H15	=IF(OR(I15={"PH","Sick","Covid"}),TIME(7,24,0),"")
E19:F19,H52,E52:F52,H41,E41:F41,H30,E30:F30,H19	E19	=SUM(E13:E17)
I19,I52,I41,I30	I19	=SUM(E19:H19)
D23,D45,D34	D23	=D12+7
H57	H57	=H55
H58	H58	=I19+I30+I41+I52
H59	H59	=H57-H58

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E19:F19,H19:I19,E30:F30,H30:I30,E41:F41,H41:I41,E52:F52,H52:I52,H57:I59,H55:I55	Cell Value	=0	text	NO
E8	Cell	contains a blank value	text	NO
E6	Cell Value	contains "Type your name in here."	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E24:E28	List	=$M$19
E46:E50	List	=$M$19
E35:E39	List	=$M$19
I13:I17	List	=$M$3:$M$4
I24:I28	List	=$M$3:$M$4
I35:I39	List	=$M$3:$M$4
I46:I50	List	=$M$3:$M$4
E13:E17	List	=$L$3:$L$4

 

[Geek Girl 007]

Hi,
I worked it out, i needed to change the number format to 11:00 instead of 11.
Thank you all for your help.
Happy New Year!!