Counting time formula

[adinek22]

Hello All,

Can you please help me with the formula wchich will count the times. I mean if for example in A1 i will have 08:30:00 PM and A2 06:10:00 AM i want in A3 formula to tell me how many hours its. Also i possible i would like in number format instead of time so for example it will say it was 8,5. i have attached the sheet as well and i need this formula from E39 - E58

[Richard Buttrey]

Hi,

One way in D40 etc..

Please Login or Register  to view this content.

[adinek22]

Thank you very much

[bebo021999]

Why 8.5? In case of rounding to nearest 30 minutes: 9h40 to 9h30, 9h50 to 10h00
E44:
=MROUND((D44-C44+(C44>D44))*24,0.5)

[Richard Buttrey]

Re #2. Sorry, that was of course a typo. I should have said 'in E40', but I guess you probably realised that.

[adinek22]

Thanks guys i does work but one more question, i have added to take off 0.5 as and half hour but is there any way i can add like in =IFERROR(((D44-C44+IF(D44<C44,1,0))*24)-0.5,) when C44 or D44 blank leave us 0, because it taking 0,5 if there isnt anything there.

[bebo021999]

May be:
Both blank returns 0
Any of both blank returns 0.5
Else, subtract.

=IF(AND(C44="",D44=""),0,IF(OR(C44="",D44=""),0.5,(D44-C44+(C44>D44))*24))

[adinek22]

it doesnt work unfortunetaly

[bebo021999]

Could you give few desired results in the sample?