24 hours schedule , nightshift - Excel - Forum Excel, VBA, VSTO, Exceltips, Excelhjälp

Sök Forumets startsida

Forum

Diskussioner

Excel

24 hours schedule , nightshift

Föregående

Nästa

2019-05-14 07:10

John

Medlem sedan: 2019-05-14

2 inlägg

24 hours schedule , nightshift

I am tryging to solve a problem at work to my chef

They have two cell exempel

Cell A3 (18.00-22:00)

Cell A4 (10:00-12:00)

I need to add cell A3 with cell A4 to know how many hours person worked . I used this form =(TEXTNUM(EXTEXT(A3;SÖK("-";A3;1)+1;5)))-(TEXTNUM(EXTEXT(A3;1;SÖK("-";A3;1)-1))) IT takeS 18:00 - 22:00 i cell A3 = 4:00 That mean that person worked 4 hours in that shift

=(TEXTNUM(EXTEXT(A4;SÖK("-";A4;1)+1;5)))-(TEXTNUM(EXTEXT(A4;1;SÖK("-";A4;1)-1))) IT takes i cell 4 10:00-12:00 = 2 That mean that person worked 42hours in that shift

. I need to work out how to calcult nightshift. Cell A finish 22:00 and Cell B starts at 10:0 0 and that means i need to calculate the hours from the person who starts at 22:00 and will finish 10:00 when the other person starts

i hope someone can help

thanks

2019-05-14 17:35

Christian

Medlem sedan: 2015-08-26

1216 inlägg

Re: 24 hours schedule , nightshift

hi
for nightshift the Logic is 1-starttime+stoptime. you could bundle it together with the dayshift formula: =om(stoptime>starttime; "dayshiftformula";"nightshiftformula") /regards Chris.

2019-05-15 21:58

John

Medlem sedan: 2019-05-14

2 inlägg

Re: 24 hours schedule , nightshift

Hi ,

Thanks

It tried to do it but it did not work . it shows error. can you write using an exempel as i did in the begning here

2019-05-17 11:16

Christian

Medlem sedan: 2015-08-26

1216 inlägg

Re: 24 hours schedule , nightshift

Hi again

Start with adding two help columns, starttime and stoptime. Otherwise youll end up with a monster formula thats both redundant, overly complicated and hard to maintain. Use your formulas to complete them.

=(TEXTNUM(EXTEXT(A3;SÖK("-";A3;1)+1;5))) should work straight of the bat, This one should give you the stoptime. Same goes for your other formula. Thereafter its quite easy to make a formula to find the timespan there between. You will want to check that excel can interpret it as a time. If its formated as a number you should get a number between 0 and 1.

Lets say you use column C for starttime and column D for stoptimes:

=om(C3>D3;1-C3+D3;D3-C3)

Regards
Chris

Sida 1 av 1

24 hours schedule , nightshift