Hour Caclulation
- Thread starter sa.1985
- Start date
Faseeh
Excel Ninja
Hi Sa.1985
Dear, Apparently the 7.30 min are correct!! They might not be if you are taking 8:30 AM for lets say 20 Feb 2012 and 01:00 AM for 21 Feb 2012. This was what i am trying to explain.
In order to confirm further, am now working on a sheet, It has times as you have mentioned in Cells A1 and B1, but when you look at the formula bar you will find that a date is automatically added to the time!! Try this out and check formula bar.
By the way what should be the correct total hours that you wanted to appear?? What was supposed to be the correct result?
Regards,
Faseeh
Dear, Apparently the 7.30 min are correct!! They might not be if you are taking 8:30 AM for lets say 20 Feb 2012 and 01:00 AM for 21 Feb 2012. This was what i am trying to explain.
In order to confirm further, am now working on a sheet, It has times as you have mentioned in Cells A1 and B1, but when you look at the formula bar you will find that a date is automatically added to the time!! Try this out and check formula bar.
By the way what should be the correct total hours that you wanted to appear?? What was supposed to be the correct result?
Regards,
Faseeh
SirJB7
Excel Rōnin
Hi, sa.1985!
I opened a new workbook, in first clean sheet typed (unquoted) in A1 "8:30", in B1 "13:00" (I don't use A.M./P.M. format, just 24h), in D1 "=B1-A1" and it displayed "4:30" that it's what you want.
So I think that there is a data or format problem. Can you please write down A1:D1 data as you see it in cell and in the input bar? And their format as well (select each cell and Ctrl-1). Or consider uploading the file.
Regards!
I opened a new workbook, in first clean sheet typed (unquoted) in A1 "8:30", in B1 "13:00" (I don't use A.M./P.M. format, just 24h), in D1 "=B1-A1" and it displayed "4:30" that it's what you want.
So I think that there is a data or format problem. Can you please write down A1:D1 data as you see it in cell and in the input bar? And their format as well (select each cell and Ctrl-1). Or consider uploading the file.
Regards!
Hi Sa.1985,
As per your case,I assume you are expecting the total working hrs to be 16:30 hrs. 8.30 am to 1.30 am.
with the assumption that he is logged off next day itself I have following solution:
A1=Start Time B1=End Time C1=Total Time D1=MyEndTime E1=MyStartTime
08:30 01:00 <formula> 11:59:59 pm 12:00:01 am
and the formula is =IF(B2<A2,(D2-A2)+(B2-E2),B2-A2)
Remember, this fails if you have time data format changed from am/pm to 24HRS
Regards,
Prasad DN
PS: Need to use with caution, that if he work more than 24 hrs, it may count from 0. example if 08:30 am started and ended next day 9.30 am it will show as only 1 hr.
As per your case,I assume you are expecting the total working hrs to be 16:30 hrs. 8.30 am to 1.30 am.
with the assumption that he is logged off next day itself I have following solution:
A1=Start Time B1=End Time C1=Total Time D1=MyEndTime E1=MyStartTime
08:30 01:00 <formula> 11:59:59 pm 12:00:01 am
and the formula is =IF(B2<A2,(D2-A2)+(B2-E2),B2-A2)
Remember, this fails if you have time data format changed from am/pm to 24HRS
Regards,
Prasad DN
PS: Need to use with caution, that if he work more than 24 hrs, it may count from 0. example if 08:30 am started and ended next day 9.30 am it will show as only 1 hr.