I am getting confused with Multi nesting could do with some pointers please.

[madmiddle]

Good morning people,

i working for a company where i work flexi time, I have over the years used many spreadsheets to try and track my time. I am now trying to create a unique spreadsheet for me to use in the several situations that i come across during the working week.

Now to my problem. in the columns that i have created i would like to have a status column that indicates what sort of of day it for example:

1 = Standard Day
2 = Holiday
3 = Half Holiday
4 = Flexday
5 = Half Flexday
6 = Half Flex Half Holiday
etc, etc

but trying to put the code into one cell for working out what time you have gained in the day is starting to confuse the hell out of me. i have managed to get them working in individual cells but trying to put the code into one i am struggling with as i always seem to find errors and can not clear them.

what i have so far is:

=IF(I4=1,(IF(U4=TRUE,Flexi_End,G4))-(IF(T4=TRUE,Flexi_Start,E4))-Full_Day-Lunch_Duration)
=IF(I4=2,0)
=IF(I4=3,IF(Q4=TRUE,(IF(U4=TRUE,Flexi_End-Lunch_End-Half_Day,G4-Lunch_End-Half_Day)),IF(R4=TRUE,(IF(T4=TRUE,Lunch_Start-Flexi_Start-Half_Day,Lunch_Start-E4-Half_Day)))))
=IF(I4=4,-Full_Day)
=IF(I4=5,IF(Q4=TRUE,(IF(U4=TRUE,Flexi_End-Lunch_End-Full_Day,G4-Lunch_End-Full_Day)),IF(R4=TRUE,(IF(T4=TRUE,Lunch_Start-Flexi_Start-Full_Day,Lunch_Start-E4-Full_Day)))))
=IF(I4=6,-Half_Day)
=IF(I4=7,0)
=IF(I4=8,0)
=IF(I4=9,0)
=IF(I4=10,((IF(G4<CORE_END,MED_END,IF(G4>Flexi_End,Flexi_End,G4)))-(IF(E4>Core_Start,med_start,IF(E4<<FLEXI_START,FLEXI_START,E4))))-FULL_DAY-LUNCH_DURATION)[ CODE]
Flexi_Start,Flexi_Start,E4))))-Full_Day-Lunch_Duration)

now for all the formulars that end with a 0 i can simplify them with the OR function but what ever i do i cannot add them all on the same line. Is there something hidden in excel that will help me create a levelled code? or any pointers or a simplier way of what i am trying to do would be much appreciated.

Thank you in advance

Andy

I am on a Windows 7 Enterprise Laptop running Office 2010.

Edit: i was going to attach the spreadsheet but can't find the function to do that.

 

[earthworm]

Upload the file here and share the link

https://app.box.com/files

 

[madmiddle]

Upload the file here and share the link

https://app.box.com/files

Right hopefully this is now done. the link is https://www.box.com/s/dslie9v1o3jkocmh58wv</SPAN></SPAN>
Sheet 1 is where it gets it's data from and on sheet 2 is what i'm trying to populate (the columns that are coloured yellow are the ones i will eventually hide)

 

[earthworm]

I have viewed your sheet , you are doing the thing too difficult
If I am not mistaken , you want to identify the type of day based on clock in and clock out (I.E Duration)

[TABLE="width: 425"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]S.no[/TD]
[TD]Days[/TD]
[TD]Clock In[/TD]
[TD]Clock Out[/TD]
[TD]Duration In Hours[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] Standard Day[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] Holiday[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] Half Holiday[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] Flexday[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] Half Flexday[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] Half Flex Half Holiday[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


With reference to above , kindly speciy the duration of working hours in front of each day , so that things can become easier to understand and work

 

[madmiddle]

I have viewed your sheet , you are doing the thing too difficult
If I am not mistaken , you want to identify the type of day based on clock in and clock out (I.E Duration)

[TABLE="width: 425"]
<TBODY>[TR]
[TD]S.no
[/TD]
[TD]Days
[/TD]
[TD]Clock In
[/TD]
[TD]Clock Out
[/TD]
[TD]Duration In Hours
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Standard Day
[/TD]
[TD]8.48
[/TD]
[TD]16.54[/TD]
[TD]7.24[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Holiday
[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Half Holiday
[/TD]
[TD]8.48 or 13.12
[/TD]
[TD]12.30 or 16.54[/TD]
[TD]3.42[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Flexday
[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]-7.24[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Half Flexday
[/TD]
[TD]8.48 or 13.12
[/TD]
[TD]12.30 or 16.54[/TD]
[TD]-3.42[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Half Flex Half Holiday
[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]-3.42[/TD]
[/TR]
</TBODY>[/TABLE]


With reference to above , kindly speciy the duration of working hours in front of each day , so that things can become easier to understand and work

First off thanks for looking.

Thats where i'm struggling really, i'm on flexi time which makes it complicated to keep track of.
standard hours are 8.48 to 16.54 with 42 minutes for lunch. Standard day is 7.24, half day is 3.42.

So if i clock in at 8.00 then i have gained 48 minutes flexi time. and i can earn flexi time between 7.00 and 18.30.

 

[earthworm]

hmmm you want the excel to identify the type of shift based on login and log out + duration of overtime shift and convert that to nearest match shift .

its quite complicated and lengthy . I think you need to make the things must simpler . Because in formula you have not used cell references which is quite lengthy to understand .

 

[madmiddle]

hmmm you want the excel to identify the type of shift based on login and log out + duration of overtime shift and convert that to nearest match shift .

its quite complicated and lengthy . I think you need to make the things must simpler . Because in formula you have not used cell references which is quite lengthy to understand .

I did it that way so i didn't have to keep going backwards and forwards to the other sheet and if i gave them a name then i would understand what the sum was. For row 4, cells V4:AE4 seem to give me the answer i want in the given situation, but it's just sticking it in the one cell that i can't seem to do.