How to have the option of working with 2 different fields - one based on the other?

horees

Board Regular
Joined
Sep 25, 2012
Messages
63
Hello everybody,</SPAN>
I’m creating an excel sheet will be used as template for data entry. One of the fields is time.</SPAN>
Unfortunately, the time unit isn’t standard here, so some people use DAYS, other use HRS.</SPAN>
In both cases, I need to have 2 fields for time for both units. So, is it possible to tell the Excel somehow if the use used the column of Time-Days, calculated the Time-Hours, and if the user used the Time-Hours, calculate the Time-Days?
At the same time, I want to keep the formulas in both fields unchanged (so if someone used the field Time-HR, I want the field still have the formula to convert to HR in cases that the user use the Time-Days)

Thank you</SPAN>

Note: The user won't work with 2 units at the same time at the same sheet...so the data entry will be using the same unit, but still we need to calculate the other.
Time-DaysTime-Hours
Case 11 (entered)24 (calculated)
Case 22 (calculated)48 (entered)

<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

horees

Board Regular
Joined
Sep 25, 2012
Messages
63

ADVERTISEMENT

Any Crazy Ideas? I don't want to force entry using either hours or days. I need to give the user the freedom to use whatever the units he likes.
We can think about formulas or vba code.

Thank you
 

horees

Board Regular
Joined
Sep 25, 2012
Messages
63

ADVERTISEMENT

so I want the user to enter either Days OR Hours (one of the other)
and the excel should calculate the other
The problem is that some users plan in hrs, while others plan in days! and in both situation, I need to calculate the other. (keeping in mind that I want some sort of cell protection to the calculated durations)
:)

Thank you

PlanDuration, DaysDuration, Hrs
A124
B248
C124
D372

<TBODY>
</TBODY>
 

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. Windows
The problem is that some users plan in hrs, while others plan in days! and in both situation, I need to calculate the other.
Why?

I would suggest something like this:
Arbeitsblatt mit dem Namen 'Tabelle1'
ABCD
1duration daysduration hoursduration (hours)
2248
32020

<tbody>
</tbody>

ZelleFormel
D2=IF(COUNT(A2:B2),IF(A2,A2*24,B2),"")

<tbody>
</tbody>
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,853
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top