Redsox15791
New Member
- Joined
- Dec 10, 2017
- Messages
- 2
Hello,
I am looking for some help creating a formula for 2 time conversions, and a Julian day converter. Please keep in mind I cannot us VBA due to workbook sharing. If you are aware of what this applies to, please keep that private.
I work for the military and while it wouldnt be wise to identify what the prupose is, for the sake of the fomula I will use the terms execution time and initiation time. Also when I say L it means Local time and Z it means Zulu time. All times are in 24 hour formatting. The first two formulas consist of 3 cells. Its simple math, but if it were so simple for everyone, I wouldnt have to do this
. Below are the 3 formulas I am having trouble with:
1. A 24 hour Zulu conversion. 1st cell would be the local time input by operator. 2nd cell would be the hour difference which we are from Zulu time, also input by the operator. For example, it is 1425L and 1825Z, so the cell would have -4. Or it is 2030L and 1530Z, so the cell would have +5. 3rd cell would calculate the difference between the 1st cell and 2nd cell.
2. An initiation time conversion based on an execution time and different options. 1st cell would be the current Z time, input by the user. 2nd cell, input by the user, would say the amount of time from the execution, which the event initiation needs to happen. For example Execution time is 1950Z and the time it needs to initiate is 5 hours and 25 minutes earlier, cell 2 would say 5+25 for 5 hours and 25 minutes. Another example is the execution is at 0155Z and the initiation event requires to be started 3 hours and 20 minutes earlier, cell 2 hould say 3+20. Cell 3, calculated, would subtract cell 2 from cell 1.
My biggest challenges with this formula were the situations like my second example going from 0155 Zulu back to the later 20 through 24 hour time formatting. Also the conversion of 60 minutes representing an hour as opposed to standard 100 representing "1", if that makes sense.
3. Based on the Zulu day changing at 0000Z, a formula that converts the zulu day to julian calendar day. 1st cell would would be zulu day of the week. I.e. "Monday". 2nd cell would say current Zulu day. In 2 characters always. E.G. "01 or 29". 3rd cell would say Month, in 3 character format. E.G. "Dec". 4th cell would say year, in 4 characters. 5th cell would be blank. 6th cell would say "Julian Day:". 7th cell would calculate the current julian day of the year based on 2nd and 3rd cells. 8th cell would be +1 day of 7th cell with 365 rolling over to 001, 3 digit format.
For the sake of productivity, uniformity and perfection, all help is much appreciated. So we can standardize and prevent/save errors.
I am looking for some help creating a formula for 2 time conversions, and a Julian day converter. Please keep in mind I cannot us VBA due to workbook sharing. If you are aware of what this applies to, please keep that private.
I work for the military and while it wouldnt be wise to identify what the prupose is, for the sake of the fomula I will use the terms execution time and initiation time. Also when I say L it means Local time and Z it means Zulu time. All times are in 24 hour formatting. The first two formulas consist of 3 cells. Its simple math, but if it were so simple for everyone, I wouldnt have to do this
. Below are the 3 formulas I am having trouble with:1. A 24 hour Zulu conversion. 1st cell would be the local time input by operator. 2nd cell would be the hour difference which we are from Zulu time, also input by the operator. For example, it is 1425L and 1825Z, so the cell would have -4. Or it is 2030L and 1530Z, so the cell would have +5. 3rd cell would calculate the difference between the 1st cell and 2nd cell.
2. An initiation time conversion based on an execution time and different options. 1st cell would be the current Z time, input by the user. 2nd cell, input by the user, would say the amount of time from the execution, which the event initiation needs to happen. For example Execution time is 1950Z and the time it needs to initiate is 5 hours and 25 minutes earlier, cell 2 would say 5+25 for 5 hours and 25 minutes. Another example is the execution is at 0155Z and the initiation event requires to be started 3 hours and 20 minutes earlier, cell 2 hould say 3+20. Cell 3, calculated, would subtract cell 2 from cell 1.
My biggest challenges with this formula were the situations like my second example going from 0155 Zulu back to the later 20 through 24 hour time formatting. Also the conversion of 60 minutes representing an hour as opposed to standard 100 representing "1", if that makes sense.
3. Based on the Zulu day changing at 0000Z, a formula that converts the zulu day to julian calendar day. 1st cell would would be zulu day of the week. I.e. "Monday". 2nd cell would say current Zulu day. In 2 characters always. E.G. "01 or 29". 3rd cell would say Month, in 3 character format. E.G. "Dec". 4th cell would say year, in 4 characters. 5th cell would be blank. 6th cell would say "Julian Day:". 7th cell would calculate the current julian day of the year based on 2nd and 3rd cells. 8th cell would be +1 day of 7th cell with 365 rolling over to 001, 3 digit format.
For the sake of productivity, uniformity and perfection, all help is much appreciated. So we can standardize and prevent/save errors.
