Hi, i am newbee at this but my question is i want to calculate the hours between shift times ie 1400 - 2200 which is 8 hours, but the 1400 - 2200 is in one (the same)cell but i want to display the calculated hours (8) in a seperate cell next to it any help would be appriciated Dave
Try
Doesn't work over midnight=((MID(A1,6,2)&":"&RIGHT(A1,2)+0)-(LEFT(A1,2)&":"&MID(A1,3,2)+0))*24
VBA Noob
_________________________________________
Credo Elvem ipsum etian vivere
_________________________________________
A message for cross posters
Please remember to wrap code.
Forum Rules
Please add to your signature if you found this link helpful. Excel links !!!
Or try this
and for over night
Please Login or Register to view this content.
Please Login or Register to view this content.
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
You need to show all leading zeros e.g. 7 AM to 3 PM would be 0700 - 1500 then you can use
=MOD(TEXT(RIGHT(A1,4),"00\:00")-TEXT(LEFT(A1,4),"00\:00"),1)*24
which works even for shifts like 2300 - 0700
Hi thanks for your quick replies but i cannot get these to work, proberly not putting the forular in the correct place. Say the "1400 - 2200" is in cell A1 and the "8" is in cell A2 do I put the formulars in cell A1 or A2, said i was new at this cheers Dave....
See a couple of examples
Formula's in A1 and A2
VBA Noob
Got it working or rather you did ha ha, but my next question is how do i stop it displaying an error message when i put off in the cell instead of a shift. If it could display a 0 in the total hours would be good or jusplay nothing would be ok cheers Dave..
Try
VBA Noob=IF(ISERROR(MOD(TEXT(RIGHT(A1,4),"00\:00")-TEXT(LEFT(A1,4),"00\:00"),1)*24),"",MOD(TEXT(RIGHT(A1,4),"00\:00")-TEXT(LEFT(A1,4),"00\:00"),1)*24)
I have tried to copy all the formulas in this thread and none work, I get a value error in the results cell. Im trying to calculate hours in non military fasion. The hours cell looks like this 9:00AM - 3:00PM and I want the results cell to say 6.00. I am not concerned with over night. Its easy to do if i use 2 cells for time in and time out, but that makes the sheet too big. Im using excel 7. any help would be great. Thanks
What if i wanted to calculate total for week from single cells as explained above?
Example: E2:K2
=MOD(TEXT(RIGHT(E2:K2,4),"00\:00")-TEXT(LEFT(E2:K2,4),"00\:00"),1)*24
did not work for me
RFernandez,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ben Van Johnson
try this
Please Login or Register to view this content.
Did not work, i will make new Thread for my question. Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks