# Calculating Time Span Formula Problem

#### cmccambridge

##### New Member
I have the following formula to calculate a span of time ie F122 has 9:00PM E122 has 3:00 PM

=IF(E122=0,"",(F122-E122)*24)

Does anyone know how to change it so that if someone works past 12AM it calculates correctly

ie 6PM-1AM

Thanks

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
You have 2 options: First, enter them as dates with time. Then you can just use your normal calcuation. Otherwise, you could use the following formula, but it only works for periods less than 24 hours. If someone worked for 28 hours, it would show only 4 hours worked...

=IF(ISBLANK(E12),"",IF(F12<E12,24+(F12-E12)*24,(F12-E12)*24))

=if(count(e22:f22)=2,mod(f22-e22,1)*24,"")

its coming up as a 0

Nevermind! input error, works great! Thanks

Hrm... my formula got lost...

=IF(ISBLANK(E12),"",IF(F12 < E12,24+(F12-E12)*24,(F12-E12)*24))<e12,24+(f12-e12)*24,(f12-e12)*24))[ code]<="" html=""></e12,24+(f12-e12)*24,(f12-e12)*24))[>

Last edited:

Replies
8
Views
200
Replies
1
Views
886
Replies
8
Views
331
Replies
6
Views
455
Replies
1
Views
167

1,206,826
Messages
6,075,094
Members
446,119
Latest member
BrianAndrews

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back