# Numbers format help



## Geek Girl 007 (Dec 3, 2022)

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.xlsxDEFGHIJ43LEAVEPUBLIC HOLIDAY44W/C4505/12/2022Hrs : MinsLeave DaysLeave hrsHrs : MinsDaysSELECT46Monday0.027:241.0PH47Tuesday11:001.0  48Wednesday11:001.0  49Thursday0.0  50Friday0.0  515222:002.07:241.05354Totals55Yearly Rollover AL (if appropriate)2714156Hrs : MinsDays57Previous AL2714158Sub Total3.23359Total268381 (3)Cell FormulasRangeFormulaH46H46=IF(OR(J46={"PH"}),TIME(7,24,0),"")I46:I50I46=IFERROR(CEILING(H46*24/10,0.5), "")H47H47=IF(OR(J47={"PH","no"}),TIME(7,24,0),"")H48:H50H48=IF(OR(J48={"PH","Sick","Covid"}),TIME(7,24,0),"")F46:F50F46=CEILING(E46*20/10,0.5)E52:F52,H52:I52E52=SUM(E46:E50)G57G57=I55I57I57=J55G58G58=E52+F52+H52I58I58=F19+I19+F30+I30+F41+I41+F52+I52G59,I59G59=G57-G58Cells with Conditional FormattingCellConditionCell FormatStop If TrueG57:J59Cell Value=0textNOJ46:J50Cell Value=0textNOH52Cell Value=0textNOH52Expression=#REF!="Covid"textNOH52Expression=#REF!="Sick"textNOH52Expression=#REF!="Toil"textNOH52Expression=#REF!="PH"textNOI52Cell Value=0textNOI52Expression=#REF!="Covid"textNOI52Expression=#REF!="Sick"textNOI52Expression=#REF!="Toil"textNOI52Expression=#REF!="PH"textNOF46:F50Cell Value=0textNOE52:F52Cell Value=0textNOE52:F52Expression=#REF!="Covid"textNOE52:F52Expression=#REF!="Sick"textNOE52:F52Expression=#REF!="Toil"textNOE52:F52Expression=#REF!="PH"textNOD52Cell Valuecontains "Excess Hours"textNOCells with Data ValidationCellAllowCriteriaE46:E50List=$N$19J46:J50List=$N$10:$N$11


----------



## MCLIFTO8 (Dec 11, 2022)

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 (Dec 12, 2022)

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.xlsxDEFGHIJK43LEAVEPUBLIC HOLIDAY44W/C4505/12/2022Hrs : MinsLeave DaysLeave hrsHrs : MinsDaysSELECT46Monday0.027:241.0PH47Tuesday11:001.0  48Wednesday11:001.0  49Thursday0.0  50Friday0.0  515222:002.07:241.05354Totals55Yearly Rollover AL (if appropriate)271414156Hrs : MinsDays57Previous AL2714158Sub Total77:24359Total6426:36381 (3)Cell FormulasRangeFormulaH46H46=IF(OR(J46={"PH"}),TIME(7,24,0),"")I46:I50I46=IFERROR(CEILING(H46*24/10,0.5), "")H47H47=IF(OR(J47={"PH","no"}),TIME(7,24,0),"")H48:H50H48=IF(OR(J48={"PH","Sick","Covid"}),TIME(7,24,0),"")F46:F50F46=CEILING(E46*20/10,0.5)E52:F52,H52:I52E52=SUM(E46:E50)G57G57=I55I57I57=J55G58G58=E52+F52+H52I58I58=F19+I19+F30+I30+F41+I41+F52+I52G59,I59G59=G57-G58Cells with Conditional FormattingCellConditionCell FormatStop If TrueF46:F50Cell Value=0textNOCells with Data ValidationCellAllowCriteriaE46:E50List=$N$19J46:J50List=$N$10:$N$11


----------



## MCLIFTO8 (Dec 12, 2022)

*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 (Jan 2, 2023)

MCLIFTO8 said:


> *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*
> ...


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.xlsxBCDEFGHIJK10LEAVEPUBLIC HOLIDAY11W/C1228/02/2022Hrs : MinsLeave hrsHrs : MinsSELECT13Monday117:24PH14Tuesday11 15Wednesday 16Thursday 17Friday7:24PH18TOTAL1922.000.0014:4822.621Cell FormulasRangeFormulaD12D12=E8H13H13=IF(OR(I13={"PH"}),TIME(7,24,0),"")H14H14=IF(OR(I14={"PH","no"}),TIME(7,24,0),"")H15:H17H15=IF(OR(I15={"PH","Sick","Covid"}),TIME(7,24,0),"")E19:F19,H19E19=SUM(E13:E17)I19I19=SUM(E19:H19)Cells with Conditional FormattingCellConditionCell FormatStop If TrueI13:I17Cell Value=0textNOH19Cell Value=0textNOH19Expression=#REF!="Covid"textNOH19Expression=#REF!="Sick"textNOH19Expression=#REF!="Toil"textNOH19Expression=#REF!="PH"textNOE19:F19Cell Value=0textNOE19:F19Expression=#REF!="Covid"textNOE19:F19Expression=#REF!="Sick"textNOE19:F19Expression=#REF!="Toil"textNOE19:F19Expression=#REF!="PH"textNOD19Cell Valuecontains "Excess Hours"textNOCells with Data ValidationCellAllowCriteriaI13:I17List=$M$3:$M$4E13:E17List=$L$3:$L$4


----------



## MCLIFTO8 (Jan 6, 2023)

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 (Jan 6, 2023)

MCLIFTO8 said:


> 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.
> ...


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.xlsxBCDEFGHIJK23456   Name: .7828/02/2022<-- Insert start date here910LEAVEPUBLIC HOLIDAY11W/C1228/02/2022Hrs : MinsLeave hrsHrs : MinsSELECT13Monday264:007:24PH14Tuesday264:00 15Wednesday 16Thursday 17Friday7:24PH18TOTAL1922.000.0014:4822.622021LEAVEPUBLIC HOLIDAY22W/C2307/03/2022Hrs : MinsLeave hrsHrs : MinsSELECT24Monday 25Tuesday 26Wednesday 27Thursday 28Friday 29300:000:000:000:003132LEAVEPUBLIC HOLIDAY33W/C3414/03/2022Hrs : MinsLeave hrsHrs : MinsSELECT35Monday 36Tuesday 37Wednesday 38Thursday 39Friday 40410:000:000:000:004243LEAVEPUBLIC HOLIDAY44W/C4521/03/2022Hrs : MinsLeave hrsHrs : MinsSELECT46Monday 47Tuesday 48Wednesday 49Thursday 50Friday 51520:000:000:000:005354Totals55Yearly Rollover AL (if appropriate)2714156Hrs : Mins57Previous AL6504:0058Sub Total542:4859Total5961:12606162631Cell FormulasRangeFormulaD12D12=E8H13,H46,H35,H24H13=IF(OR(I13={"PH"}),TIME(7,24,0),"")H14,H47,H36,H25H14=IF(OR(I14={"PH","no"}),TIME(7,24,0),"")H15:H17,H48:H50,H37:H39,H26:H28H15=IF(OR(I15={"PH","Sick","Covid"}),TIME(7,24,0),"")E19:F19,E52:F52,E41:F41,E30:F30,H19E19=SUM(E13:E17)I19,I52,I41,I30I19=SUM(E19:H19)D23,D45,D34D23=D12+7H30,H52,H41H30=SUM(H24:H27)H57H57=H55H58H58=I19+I30+I41+I52H59H59=H57-H58Cells with Conditional FormattingCellConditionCell FormatStop If TrueI46:I50Cell Value=0textNOI35:I39Cell Value=0textNOI24:I28Cell Value=0textNOH52Cell Value=0textNOE52:F52Cell Value=0textNOD52Cell Valuecontains "Excess Hours"textNOH41Cell Value=0textNOE41:F41Cell Value=0textNOD41Cell Valuecontains "Excess Hours"textNOH30Cell Value=0textNOE30:F30Cell Value=0textNOD30Cell Valuecontains "Excess Hours"textNOI13:I17Cell Value=0textNOH19Cell Value=0textNOE19:F19Cell Value=0textNOD19Cell Valuecontains "Excess Hours"textNOH57:I59Cell Value=0textNOE8Cellcontains a blank value textNOE8Cell Valuecontains "Type your name in here."textNOE6Cell Valuecontains "Type your name in here."textNOCells with Data ValidationCellAllowCriteriaE24:E28List=$M$19E46:E50List=$M$19E35:E39List=$M$19I13:I17List=$M$3:$M$4I24:I28List=$M$3:$M$4I35:I39List=$M$3:$M$4I46:I50List=$M$3:$M$4E13:E17List=$L$3:$L$4


----------



## Geek Girl 007 (Jan 6, 2023)

Geek Girl 007 said:


> 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.xlsxBCDEFGHIJK23456   Name: .7828/02/2022<-- Insert start date here910LEAVEPUBLIC HOLIDAY11W/C1228/02/2022Hrs : MinsLeave hrsHrs : MinsSELECT13Monday264:007:24PH14Tuesday264:00 15Wednesday 16Thursday 17Friday7:24PH18TOTAL1922.000.0014:4822.622021LEAVEPUBLIC HOLIDAY22W/C2307/03/2022Hrs : MinsLeave hrsHrs : MinsSELECT24Monday 25Tuesday 26Wednesday 27Thursday 28Friday 29300:000:000:000:003132LEAVEPUBLIC HOLIDAY33W/C3414/03/2022Hrs : MinsLeave hrsHrs : MinsSELECT35Monday 36Tuesday 37Wednesday 38Thursday 39Friday 40410:000:000:000:004243LEAVEPUBLIC HOLIDAY44W/C4521/03/2022Hrs : MinsLeave hrsHrs : MinsSELECT46Monday 47Tuesday 48Wednesday 49Thursday 50Friday 51520:000:000:000:005354Totals55Yearly Rollover AL (if appropriate)2714156Hrs : Mins57Previous AL6504:0058Sub Total542:4859Total5961:12606162631Cell FormulasRangeFormulaD12D12=E8H13,H46,H35,H24H13=IF(OR(I13={"PH"}),TIME(7,24,0),"")H14,H47,H36,H25H14=IF(OR(I14={"PH","no"}),TIME(7,24,0),"")H15:H17,H48:H50,H37:H39,H26:H28H15=IF(OR(I15={"PH","Sick","Covid"}),TIME(7,24,0),"")E19:F19,E52:F52,E41:F41,E30:F30,H19E19=SUM(E13:E17)I19,I52,I41,I30I19=SUM(E19:H19)D23,D45,D34D23=D12+7H30,H52,H41H30=SUM(H24:H27)H57H57=H55H58H58=I19+I30+I41+I52H59H59=H57-H58Cells with Conditional FormattingCellConditionCell FormatStop If TrueI46:I50Cell Value=0textNOI35:I39Cell Value=0textNOI24:I28Cell Value=0textNOH52Cell Value=0textNOE52:F52Cell Value=0textNOD52Cell Valuecontains "Excess Hours"textNOH41Cell Value=0textNOE41:F41Cell Value=0textNOD41Cell Valuecontains "Excess Hours"textNOH30Cell Value=0textNOE30:F30Cell Value=0textNOD30Cell Valuecontains "Excess Hours"textNOI13:I17Cell Value=0textNOH19Cell Value=0textNOE19:F19Cell Value=0textNOD19Cell Valuecontains "Excess Hours"textNOH57:I59Cell Value=0textNOE8Cellcontains a blank value textNOE8Cell Valuecontains "Type your name in here."textNOE6Cell Valuecontains "Type your name in here."textNOCells with Data ValidationCellAllowCriteriaE24:E28List=$M$19E46:E50List=$M$19E35:E39List=$M$19I13:I17List=$M$3:$M$4I24:I28List=$M$3:$M$4I35:I39List=$M$3:$M$4I46:I50List=$M$3:$M$4E13:E17List=$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.xlsxBCDEFGHIJK23456   Name: .Type your name in here.78<-- Insert start date here910LEAVEPUBLIC HOLIDAY11W/C1200/01/1900Full DayAd HocHrs : MinsSELECT13Monday264:007:24PH14Tuesday264:00 15Wednesday 16Thursday 17Friday7:24PH18TOTAL19528:000:0014:48542:482021LEAVEPUBLIC HOLIDAY22W/C2307/01/1900Full DayAd HocHrs : MinsSELECT24Monday 25Tuesday 26Wednesday 27Thursday 28Friday 29TOTAL300:000:000:000:003132LEAVEPUBLIC HOLIDAY33W/C3414/01/1900Full DayAd HocHrs : MinsSELECT35Monday 36Tuesday 37Wednesday 38Thursday 39Friday 40TOTAL410:000:000:000:004243LEAVEPUBLIC HOLIDAY44W/C4521/01/1900Full DayAd HocHrs : MinsSELECT46Monday 47Tuesday 48Wednesday 49Thursday 50Friday 51TOTAL520:000:000:000:005354Totals55Yearly Rollover AL (if appropriate)6504:004156Hrs : Mins57Previous AL6504:0058Sub Total542:4859Total5961:12606162631Cell FormulasRangeFormulaD12D12=E8H13,H46,H35,H24H13=IF(OR(I13={"PH"}),TIME(7,24,0),"")H14,H47,H36,H25H14=IF(OR(I14={"PH","no"}),TIME(7,24,0),"")H15:H17,H48:H50,H37:H39,H26:H28H15=IF(OR(I15={"PH","Sick","Covid"}),TIME(7,24,0),"")E19:F19,H52,E52:F52,H41,E41:F41,H30,E30:F30,H19E19=SUM(E13:E17)I19,I52,I41,I30I19=SUM(E19:H19)D23,D45,D34D23=D12+7H57H57=H55H58H58=I19+I30+I41+I52H59H59=H57-H58Cells with Conditional FormattingCellConditionCell FormatStop If TrueE19:F19,H19:I19,E30:F30,H30:I30,E41:F41,H41:I41,E52:F52,H52:I52,H57:I59,H55:I55Cell Value=0textNOE8Cellcontains a blank value textNOE6Cell Valuecontains "Type your name in here."textNOCells with Data ValidationCellAllowCriteriaE24:E28List=$M$19E46:E50List=$M$19E35:E39List=$M$19I13:I17List=$M$3:$M$4I24:I28List=$M$3:$M$4I35:I39List=$M$3:$M$4I46:I50List=$M$3:$M$4E13:E17List=$L$3:$L$4


----------



## Geek Girl 007 (Saturday at 3:02 AM)

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!!


----------

