0

This is the setup:

1. work days Mo Tu Wed Thu, Fri different hours

2. shifts daily MO to THU:

  • early 5:15 - 13:30 = 8:15
  • Late 13:30 - 21:30 = 8:00
  • night 21:30 - 5:15 = 7:45

3. Friday hours

  • early 5:15 - 12:15 = 7:00
  • late 12:15 - 19:00 = 6:45
  • night 19:00 - 3:00 = 7:45

4. Flexible work

I have a flexible work schedule.  Let's say if I input 14:45 I want to know when do I stop working (not counting overtime) but also I want to know when I can start again after 8h because I only need 8h between two shifts (not every day but it happens) so I want to only input my starting hours every day and let Excel do the rest.  It almost works but I'm stuck.

╔════╦════════╦═══════════════════════╦═══════════╦═══════════╦═══════════════════════╦══════════════════════════════════════════╗
║    ║    A   ║           B           ║     C     ║     D     ║ E                     ║ F                                        ║
╠════╬════════╬═══════════════════════╩═══════════╩═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 1  ║        ║                 HOUR MON - THU                ║ 8 hours between       ║ TRUE/FALSE                               ║
╠════╬════════╬═══════════════════════╦═══════════╦═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 2  ║        ║         Early         ║    Late   ║   Night   ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 3  ║   MON  ║         10:30         ║   input   ║   input   ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 4  ║   TU   ║          1:00         ║   input   ║   input   ║ =SUM(B9;TIME(8;00;0)) ║ =IF(B4<E4;"not 8H between";"8H between") ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 5  ║   WED  ║         input         ║   input   ║   input   ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 6  ║   THU  ║         input         ║   input   ║   input   ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 7  ║   fri  ║         input         ║   input   ║   input   ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 8  ║        ║         until         ║   until   ║   until   ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 9  ║   MO   ║ =SUM(B3;TIME(8;15;0)) ║  Formula  ║  Formula  ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 10 ║   TU   ║ =SUM(B4;TIME(8;15;0)) ║  formula  ║  Formula  ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 11 ║   WED  ║        Formula        ║  Formula  ║  Formula  ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 12 ║   THU  ║        Formula        ║  Formula  ║  Formula  ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 13 ║   FRI  ║        Formula        ║  Formula  ║  Formula  ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 14 ║ MO-THU ║       5:15-13:30      ║   H-Info  ║   H-info  ║                       ║                                          ║
╠════╣        ╠═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 15 ║        ║          8:15         ║ H to work ║ H to work ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 16 ║   FRI  ║       5:15-12:15      ║   H-Info  ║   H-Info  ║                       ║                                          ║
╠════╣        ╠═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║ 17 ║        ║          7:00         ║ H to work ║ H to work ║                       ║                                          ║
╠════╬════════╬═══════════════════════╬═══════════╬═══════════╬═══════════════════════╬══════════════════════════════════════════╣
║    ║        ║                       ║           ║           ║                       ║                                          ║
╚════╩════════╩═══════════════════════╩═══════════╩═══════════╩═══════════════════════╩══════════════════════════════════════════╝

So i learned allot already, the input fields are the fields i put the hours in, the output are formulas that generate the hours to work, the 8h between work, but instead of just saying the 8 hours between shifts i would like to see it saying it's not 8h between and that IF formula is not working, it only prints 1 of the options true or false, mostly it says its false even if its true. Still i think my formula for the True/False statement should be =IF(B4>=E4;"8H between";"not 8H between") Because if the difference = 8H it's true

before i used things like =SUM(b3:B16) where B3 would be the input hours and B16 would have the time 8:15. if i can get 1 column to work i can do al fields for 3 persons working 3 shifts. is this more clear? btw count the ABC and 123 i put in.

Xtreambart Bart
  • 1
  • (1) Please don’t post images of text.  Post a textual representation of your data, as was done here, here, here, here, here and here; use the Format Text as Table or the Plain Text Tables generator site if you want. … (Cont’d) – Scott - Слава Україні Oct 03 '18 at 22:11
  • (Cont’d) …  Include the output data you expect from your input data (clearly identifying which is which) and give a clearer description of the computation you want Excel to do.  (2) If you must post an image, (2a) use English in the image if at all possible; otherwise, provide a translation in your question text, and (2b) don’t shrink it.  I looked at your image, and I had to magnify it just to read it without straining my eyes.  (And try to remove, or at least tone down, color.  I had especial difficulty reading the text on the dark green background.)  … (Cont’d) – Scott - Слава Україні Oct 03 '18 at 22:11
  • (Cont’d) …  (3) Images can be useful as illustrations.  I can’t tell what your image illustrates.  Most of the numbers are in the question text already (with explanations in English ), and I don’t understand the others.  (4) I sort-of understand what you’re asking, but not clearly enough to answer it.  Please explain in detail what you want Excel to do.  (You probably need about three times as many words as you’re using now.)  And give an example of what you want to input and what output you want from Excel.  (You might believe that you’ve already done that, in your picture, … (Cont’d) – Scott - Слава Україні Oct 03 '18 at 22:12
  • (Cont’d) …  but, as I hinted earlier, I can’t tell which is which.) (5) You say “It almost works but I'm stuck.”  *What* almost works?  If you have formulas (or VBA), show us that (as text), and not just a sheet full of numbers.  Where are you stuck? … … … … … … … … … … … … … … … … … … Please do not respond in comments; [edit] your question to make it clearer and more complete. – Scott - Слава Україні Oct 03 '18 at 22:12
  • Thanks for putting so much work into your question.  I’m sorry, but I still don’t understand it.  I don’t “kinda get” what you’re saying.  I forgot to mention in my previous comments that it helps a lot to include column headers and row labels in any representation of your data.  You cite cells by name, and I can’t tell which ones they are.  I’m trying to count the rows; your references to them seem to be off by 1 — in both directions!  For example, your data sheet seems to have =SUM(B10;+8h) in cell E4, but you say E5 =SUM(B10;C16) —  … (Cont’d) – Scott - Слава Україні Oct 05 '18 at 04:05
  • (Cont’d) … and you talk about Monday, but the (first) Monday row is the *3rd* row. My best guess (from looking at your data) is that B10 is one of the cells that say Till (which I guess is a constant string / label) and Row 16 is the totally blank row at the bottom, so I have no idea why you’re trying to add them. But you say “(outputfield b10) =SUM(B4;TIME(8;15;0))” and “B10 = the end time of Monday”, but you have =SUM(B4;TIME(8;15;0)) in what looks like the 11th row (which does seem to be a Monday row). And if the (second) Monday row is row 10, then the totally blank row … (Cont’d) – Scott - Слава Україні Oct 05 '18 at 04:05
  • (Cont’d) … at the bottom is row 15, and row 16 isn’t even in the picture. I don’t know why you have MO-Fri at Rows 3-9 and again at Rows 11-15. I guess the cells that say input h are meant to be blank cells where you enter data, but you don’t explain that. You talk about inputting “14:45”, but you don’t say where you want to put it or what (specific) results you want to get from it. You say “I want to know when do I stop working … but also I want to know when I can start again after 8h”, but you also say “The only thing I can get to work … is printing the time with 8h difference”, … (Cont’d) – Scott - Слава Україні Oct 05 '18 at 04:06
  • (Cont’d) …  so it’s really unclear what you need help with. And, if you know how to add 8 hours to a time, why don’t you just tell “Forward Ed” that I was right, and his answer is telling you information you already know? (And yet you show =SUM(B10;+8h), which is not correct.) And you’re now talking about adding 8:15 (instead of 8 hours), which is confusing. You’re talking about output fields turning blank if there is no input (see this), “a true false statement that turns green or red”,  … (Cont’d) – Scott - Слава Україні Oct 05 '18 at 04:07
  • (Cont’d) …  and displaying a message 8H between or not 8H between, none of which was in the original question. OK, the original question talked about the 8 hour interval, but it sounded like you wanted a time, not a message. This is a puzzle: if I is your input, and J=I+8h, and K=J+8h, how can there not be 8 hours between J and K? If you’re comparing K (E5?) to Z (B5?), what is Z? Does it have something to do with the fixed shift schedules? (Why did you even tell us about the shift schedules? What do they have to do with your question? … (Cont’d) – Scott - Слава Україні Oct 05 '18 at 04:07
  • (Cont’d) … You say (in a comment) that you “steer a group of people” — I guess you mean that you manage them, or direct them, or supervise them — what do *they* have to do with the question?)  … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … … …  I don’t see anything really hard here.  The hardest part of your question is understanding it.  I hope you can explain it clearly, because we’ll probably be able to answer it then. – Scott - Слава Україні Oct 05 '18 at 04:08
  • i edited it again, i steer people in 3 shifts that's why i got the 3 columns of the 3 shifts, still they have flexible shifts. The most important peace where i'm stuck is a true/false statement saying it's not 8h between, i can get the minimum starting hour to work witch is stopping hour previous day +8H when i do an if statement on the cel with stoptimeprevday+8h and the starting hours next day it does not give me a true or false statement it says it's false most of the time even when it's true – Xtreambart Bart Oct 06 '18 at 16:24

1 Answers1

1

To find out when 8 hours later will be, use the following formula:

=Q3+TIME(8,0,0)

Where Q3 has the time you want to reference.

alternatively since excel stores time as fractions of a day and days as integers since January 1st 1900 (day 1) you could convert 8 hours to a decimal format and add that instead of using the time formula.

=Q3+8/24

POC

Forward Ed
  • 1,524
  • FYI, the question doesn’t say “I need to add eight hours to a time.”  The question says ‘‘I’m having trouble making a special work calendar in Excel, with scheduled shifts that follow the same pattern Monday through Thursday, and a different one on Friday.  I have a flexible work schedule [I work at times other than the standard, scheduled shifts].  I want to know when do I stop working and when I can start again.’’  Either the OP gave us over a hundred words that are irrelevant to his question, or the question is more complicated than you think it is.  … (Cont’d) – Scott - Слава Україні Oct 03 '18 at 22:26
  • (Cont’d) …  P.S.  I don’t understand what he is asking, but I suspect that your answer won’t help him. – Scott - Слава Україні Oct 03 '18 at 22:26
  • @Scott i misread 8 hours too many times, thought they wanted to know the end time 8 hours after they started and again 8 hours after their end time so they could start again. Either way the concept of how to add time is there, it just needs to be adjusted to suit the number of hours that correponds to them – Forward Ed Oct 03 '18 at 22:43
  • Well i follow items for work, i get the hour the item wil be in the factory and that's the time i start working, i also steer a group of people with a time table i do manually, so i wanted to automate it. i can steer those people friday for the next week. they can start in one of the 3 shifts and have to respect the hours in those shifts. But it should also warn me if i input the next days hours the difference from ending 1 shift and the starting of the other shift has to be 8h, sorry that i'm not that good in english but i hope you kinda get what i'm telling. – Xtreambart Bart Oct 04 '18 at 13:21