gunder1225
New Member
- Joined
- Aug 25, 2017
- Messages
- 1
Hi!
I'm not too familiar with excel formulas, and I've been banging my head on my keyboard trying to figure this out. I'm trying to create a calculator where someone inserts a time (any time, between (12:00 am - 11:59 pm) and then the calculator adds 2 and a half hours to that time, then two and a half hours would be added two more times.
Ex1: Person enters 9:00 am, calculator adds 2:30, so it's 11:30 am, then the next cell it adds another 2:30, so it's 2:00 pm, and then the next cell it adds the final 2:30, so it's 4:30 pm.
The first tricky part for me is trying to keep times between 9 am and 6 pm (work hours). If the person entering the time enters 8:00 am or 6:24 pm, the time should add the 11:30 to 9:00 am, so that the first calculated time is 11:30 am (the start of the work day).
The second tricky part is if the 2:30 added to a time passes 6:00 pm, to then add that leftover time to 9:00 am.
Ex2: Person enters 5:30 pm, plus the 2:30 would be 8:00 pm, but instead, I want it to read as 11:00 am.
My layout in excel as in columns A and B and looks as follows:
A1 (header) B1 (header)
A2 (Step 1 - Time Entered) B2 (Time entered)
A3 (Step 2) B3 (formula)
A4 (Step 3) B4 (formula)
A5 (Step 4) B5 (formula)
In B3 I entered a long messy If/And, it works perfectly, the issue is I cannot get the next two steps to work the same way, also I'm SURE there's a slightly easier, less messy way to accomplish this
Formula entered into B3: =IF(AND(B2>=H100,B2<=A100),A99,IF(AND(B2>A100,B2<c100),b2+e100,if(and(b2>C100,B2<b100),d100-b100+a100,if(and(b2>=B100,B2<b99),a99))))
List of 99/100 cell references in order they appear in formula:
H100 = 12:00 AM
A100 = 9:00 AM
A99 = 11:30 AM
C100 = 3:30 PM
E100 = 2:30 (no am or pm)
B100 = 6:00 PM
D100 = B2 (the time manually entered) + 2:30
B99 = 11:59 PM
If you've taken the time to read this loooong post I really appreciate it! And if anything is unclear, please let me know!
And thank you in advance!</b99),a99))))
</b100),d100-b100+a100,if(and(b2></c100),b2+e100,if(and(b2>
I'm not too familiar with excel formulas, and I've been banging my head on my keyboard trying to figure this out. I'm trying to create a calculator where someone inserts a time (any time, between (12:00 am - 11:59 pm) and then the calculator adds 2 and a half hours to that time, then two and a half hours would be added two more times.
Ex1: Person enters 9:00 am, calculator adds 2:30, so it's 11:30 am, then the next cell it adds another 2:30, so it's 2:00 pm, and then the next cell it adds the final 2:30, so it's 4:30 pm.
The first tricky part for me is trying to keep times between 9 am and 6 pm (work hours). If the person entering the time enters 8:00 am or 6:24 pm, the time should add the 11:30 to 9:00 am, so that the first calculated time is 11:30 am (the start of the work day).
The second tricky part is if the 2:30 added to a time passes 6:00 pm, to then add that leftover time to 9:00 am.
Ex2: Person enters 5:30 pm, plus the 2:30 would be 8:00 pm, but instead, I want it to read as 11:00 am.
My layout in excel as in columns A and B and looks as follows:
A1 (header) B1 (header)
A2 (Step 1 - Time Entered) B2 (Time entered)
A3 (Step 2) B3 (formula)
A4 (Step 3) B4 (formula)
A5 (Step 4) B5 (formula)
In B3 I entered a long messy If/And, it works perfectly, the issue is I cannot get the next two steps to work the same way, also I'm SURE there's a slightly easier, less messy way to accomplish this
Formula entered into B3: =IF(AND(B2>=H100,B2<=A100),A99,IF(AND(B2>A100,B2<c100),b2+e100,if(and(b2>C100,B2<b100),d100-b100+a100,if(and(b2>=B100,B2<b99),a99))))
List of 99/100 cell references in order they appear in formula:
H100 = 12:00 AM
A100 = 9:00 AM
A99 = 11:30 AM
C100 = 3:30 PM
E100 = 2:30 (no am or pm)
B100 = 6:00 PM
D100 = B2 (the time manually entered) + 2:30
B99 = 11:59 PM
If you've taken the time to read this loooong post I really appreciate it! And if anything is unclear, please let me know!
And thank you in advance!</b99),a99))))
</b100),d100-b100+a100,if(and(b2></c100),b2+e100,if(and(b2>
