Cell value displays correctly, but incorrectly consists of date and time when clicked on

[inactiveUser333473]

Hi, I have cell which correctly displays "40:47" meaning 40 hours and 47 minutes. Which is the total hours worked that week. When I click on the cell I can see that excel has decided that this 40:47 is actually a date and time of "01/01/1900 16:47:00". This creates a problem for me when I try to calculate the difference between total time worked and total time that should have been worked.

 

[Fluff]

When I click on the cell I can see that excel has decided that this 40:47 is actually a date and time of "01/01/1900 16:47:00"

That's because it is, time is just a decimal fraction of day with 1 hour being 1/24th of a day. Therefore any time over 24 hours will be x days & y hours.

What is your current formula? Also can you post some sample data using the Xl2BB add-in

 

[inactiveUser333473]

Thanks,

C1 = hours actually worked
C2 = scheduled hours
C3 = balance
I was just using C2-C1 hoping to get the amount of minutes difference but it's wildly inaccurate. I don't have sample data as the sheet belongs to another co-worker that I was helping (I'm no longer at work)

 

[Fluff]

Is the scheduled hours more or less than 40 hours?

 

[inactiveUser333473]

In example, scheduled hours is 40 hours, actual worked hours is 40 hours 47 mins, so the difference should be 47 mins, but I get 7 hours, 13 mins

 

[jtakw]

Hi,

Do you need to differentiate hours Over and Under ???

Book3.xlsx
	B	C	D
1	Worked hrs	38:30:00	40:47:00
2	Schuduled hrs	40:00:00	40:00:00
3	Difference	1:30:00	0:47:00
Sheet1060

Cell Formulas
Range	Formula
C3:D3	C3	=(MAX(C2,C1)-MIN(C2,C1))

 

[Fluff]

How about

+Fluff 1.xlsm
	C
1	40:47:00
2	40:00:00
3	0:47:00
List

Cell Formulas
Range	Formula
C3	C3	=C1-C2

 

[inactiveUser333473]

This is exactly what I had, but the difference was calculated at 07:13:00 instead of 0:47:00

 

[inactiveUser333473]

I will try this tomorrow, thanks all. I think that's enough for now. I'll try again tomorrow and repost with sample if it's still not working. I'm very grateful Fluff and jtakw for the help

 

[Fluff]

Ok