• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Schedule formula

B

Bankonit4

New Member
Hello Excel Masters,


I have searched through the fantastic database we have here but I'm afraid my question may be so elementary that perhaps it hasn't been asked.


I am creating a schedule for work and would like excel to calculate the length of a shift in a particular cell and deliver it to another cell (say the one below it). For example cell A1 contains the shift "9:00-1:00" and I'd like the cell below to say 4 (hrs). Also, will excel automatically calculate a fraction of a shift? Say "9:00-1:15" would be 4.25.


Thank you for your consideration.
 
Bankonit4


Firstly, Welcome to the Chandoo.org forums


My advice, put the two times in seperate cells

Then simply =end time - start time

Format all cells as h:mm
 
Thank you Hui!


If you know of another way to make it calculate in the individual cell that would be my ideal situation since using multiple cells would be too large for what I'm trying to accomplish.


Thank you again.
 
Hi ,


Try this :


=24*IF(1*RIGHT(A1,LEN(A1)-FIND("-",A1))<1*LEFT(A1,FIND("-",A1)),0.5+1*RIGHT(A1,LEN(A1)-FIND("-",A1))-1*LEFT(A1,FIND("-",A1)),1*RIGHT(A1,LEN(A1)-FIND("-",A1))-1*LEFT(A1,FIND("-",A1)))


I assume that all times greater than 1:00:00 PM will be entered as just hh:mm , without the AM/PM suffix , and hh will be between 1 and 11.


I do not know whether you will have a starting time which is in the early hours of the morning , such as 02:00:00 AM , and the end time will be in the evening , such as 7:00 , since in this case , how do we differentiate between 7:00 AM and 7:00 PM ?


Narayan
 
Here is another approach:

Assuming your timeperiod string is in A1, the following formula would return the difference in time format (i.e. "hh:mm")

=TEXT(TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),101,100))+ 0.5 -TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),1,100)), "hh:mm")


The assumption being made is that both start time and end time are within the same day, and that the second one is in the afternoon.


So, 9:00-1:00 would return 4:00, indicating that there is a difference of 4 hours and 0 minutes.


The formula adds 12 hours (0.5) to the time segment after the "-".


If you are able to indicate am or pm in the timeperiod string, you could modify the formula to remove the addition of 12 hours. Since the formula subtracts the second timesegment from the first, ensure that each segment is a valid time format.


Sample output:

[pre]
Code: 
TimePeriod	Difference
9:00-1:00	04:00
8:00-2:00	06:00
7:00-5:00	10:00
9:00-2:15	05:15
[/pre]
Cheers,

Sajan.
 
Sajan - problem with that is that it will give an incorrect answer if the end time falls in the morning. e.g. 9:00-10:00 shows as 13 hours with your formula.
 
Hi Jeff ,


Sajan must be aware of the problem , since he mentions this in his post :



The assumption being made is that both start time and end time are within the same day, and that the second one is in the afternoon.


The formula adds 12 hours (0.5) to the time segment after the "-".



Narayan
 
Ah, yes. Missed that bit. Personally I wouldn't risk this, because while the spreadsheet designer might be aware of the limitation, someone using it or inheriting it later on might not.
 
Hi Jeff,

As I mentioned in my post above, the OP would be better off indicating AM/PM as part of the text, since it would remove the need for interpretation. Even better would be to include the date as well.


-Sajan.
 
Hi All,


This one as well:


Code: 
=IF((ABS(LEFT(A1,FIND("-",A1)-1)))>(ABS(MID(A1,FIND("-",A1)+1,6))),0.5-(ABS(LEFT(A1,FIND("-",A1)-1)))+(ABS(MID(A1,FIND("-",A1)+1,6))),(ABS(MID(A1,FIND("-",A1)+1,6)))-(ABS(LEFT(A1,FIND("-",A1)-1))))


Format cell as hh:mm, hopefully works :)


Faseeh
 
Thank you to all of you for your help! This was absolutely what I was looking for. While each suggestion was helpful Narayank happened to fit what I needed. Thank you again to everyone who replied. I appreciate you taking the time to help me with my dilemma.


If it's not too much I have one last question. If I sum each cell in a row of say 7 (for a 5 day work week plus 2 days off) and I need to omit text in 2 days (cells) say for instance the word "OFF", will I need to modify Narayank's formula?
 
Hi ,


Sorry , but I have not understood your question ; can you give an example of the data that you will have ?


Do you mean to say that in 5 out of 7 cells , you will have the text strings which have times , while the remaining 2 will be blank ?


If you can just copy + paste the data you have in the row of 7 you have mentioned , it will be easier to visualize your requirements.


Narayan
 
9:00-2:15 9:00-2:16 9:00-2:17 9:00-2:18 9:00-2:19 Off Off


5.25 5.266666667 5.283333333 5.3 5.316666667 #VALUE! #VALUE!

I want to type text in the cells when the employee is off on that particular day of the week. How do I have the formula omit "off", or count text as a "zero"?
 
Hi ,


Can you not just check for the text "Off" , and skip it ?


=IF(A1="Off","",24*IF(1*RIGHT(A1,LEN(A1)-FIND("-",A1))<1*LEFT(A1,FIND("-",A1)),0.5+1*RIGHT(A1,LEN(A1)-FIND("-",A1))-1*LEFT(A1,FIND("-",A1)),1*RIGHT(A1,LEN(A1)-FIND("-",A1))-1*LEFT(A1,FIND("-",A1))))


Narayan
 
You must log in or register to reply here.
Back
Top